Running Python via Excel VBA —a Case of Time Series Resampling | by Himalaya Bir Shrestha | Apr, 2023
A comprehensive assessment of time series resampling of solar irradiance using VBA, using Python, and using Python via Excel VBA
Recently, I have been working with Excel-based interface and models frequently. During this time, I became acquainted with the Visual Basic for Application (VBA) for Office, which is a powerful programming language to extend Office applications. VBA is used to automate repetitive tasks, extend user interactions, and interact between different Office applications, making the routine tasks more efficient and effective.
With a decent programming background in Python, it took me relatively steep learning curve towards the beginning to learn different features and functionalities of VBA, which gradually turned less steep with time. I have realized that learning VBA is very useful since it interacts with Excel workbook directly and can automate the tasks on the workbook itself that otherwise would require coding in a different programming language. However, if it is possible to run a script in a different programming language (e.g., Python) within Excel VBA, it can be even more helpful in automating our routine tasks.
In this post, I will share my experience of using Excel VBA and Python to perform a simple task — time series resampling of solar irradiance data. Furthermore, I am going to present how a Python script can be run via Excel VBA to perform the same task. Let’s get started.
Data
The data used is hourly All Sky Surface Shortwave Downward Irradiance (ALLSKY_SFC_SW_DWN) for 2020 (UTC time zone) downloaded from NASA Power website for four cities: Chitwan (Nepal), New York (USA), Sydney (Australia), and Bonn (Germany). The data was accessed and downloaded using the API service of NASA Power with a Python script, which I intend to document in a different post.
Solar irradiance is the power per unit area (W/m2) obtained from the sun in the form of electromagnetic radiation in the wavelength range of the measuring instrument. Solar irradiance integrated over a time period gives the solar irradiation, referred to as solar insolation (Wh/m2).
According to the definition of NASA Power, the parameter used All Sky Surface Shortwave Downward Irradiance (ALLSKY_SFC_SW_DWN) refers to the total solar irradiance (direct plus diffused) incident on a horizontal plane at the surface of the earth under all sky conditions. An alternative term for total solar irradiance is Global Horizontal Irradiance (GHI). Since the values are hourly, its unit is Wh/m².
Data in Excel file
Since 2020 was a leap year, I obtained the hourly solar irradiance values for 8784 hours for the four cities. These values are placed in column B, C, D and E of the Excel file as shown below.
To get the values in column A in date format, the following formula was used in Excel (e.g., in cell A2):
=TEXT(DATE(2020,1,1)+ROW(A1)/24, “yyyy-mm-dd hh:mm:ss”)
ROW(A1)/24 is used to get the value of hour within a day (0–23 hr).
Also, I named cell A1 as datetime.
Plotting the original hourly solar irradiance data for 2020 for the four cities in Excel looks as follows:
Time series resampling refers to converting the frequency level of time series data. In simple words, resampling is a time-based groupby, followed by a reduction method on each of the groups. The data must have a datetime-like index for this purpose.
1. Time Series Resampling using Excel VBA
There is no default function in Excel for time series resampling. Therefore, I wrote a couple of subroutines to get monthly and hourly average values as described in the sections a and b respectively below.
a. VBA code for converting hourly values to average monthly frequency
This section describes the code snippet (below) used to convert hourly value to average monthly value in VBA.
An array is a group of variables in VBA. The default lower bound of an array element in VBA is 0. Mentioning Option Base 1 on the top of the sub-routine changes the lower bound of array element to 1. I defined an array called columns(4) as a group of strings containing 4 variables. And I passed the strings B, C, D and E within this array.
Cell A1 in Excel is defined as a named cell datetime. To refer to this cell in VBA, I declared datetime as a range and assigned it to the range with same name in Excel.
To refer to the month from the datetime column in Excel, I used the MONTH() function and assigned it to an integer variable called mnth. To loop through hourly values in each row, I declared another integer called row. Finally, I declared sum and num_hours to calculate the monthly average values.
'Use Option Base 1 before this subroutine if you want to start the list from 1 instead of 0.
'https://excelchamps.com/vba/arrays/
Option Base 1
Sub GetMonthlyAverage()'defining an array for 4 strings
Dim columns(4) As String
columns(1) = "B"
columns(2) = "C"
columns(3) = "D"
columns(4) = "E"
'Refer to cell A1
Dim datetime As Range
Set datetime = Range("datetime")
'Definining mnth because Month is a function
Dim mnth As Integer
Dim row As Integer
Dim sum As Double
Dim num_hours As Double
Next, I created a for-loop to iterate through the columns for values for each city. Within this for-loop, there are two other nested loops to iterate through each month and hour of the year, respectively. The sum aggregates the hourly solar irradiance values for each month, and the num_hours aggregates the number of hours in each month. Finally, by dividing the sum with num_hours, monthly average values of solar irradiance are obtained for each month for each city.
'Loop through column for each city
For Each column In columns'Loop through each month of the year
For mnth = 1 To 12
sum = 0
num_hours = 0
'Loop through each row
For row = 2 To 8785
If MONTH(Cells(row, datetime.column)) = mnth Then
Range(column & row).Interior.Color = RGB(255, 255, 0)
num_hours = num_hours + 1
sum = sum + Range(column & row).Value
End If
Next row
Range(column & mnth).Offset(1, 7).Value = sum / num_hours
Next mnth
Next column
End Sub
As a note, Range(column & row).Interior.Color = RGB(255, 255, 0) highlights each cell with yellow color while iterating through the row (city) and column (mnth).
b. VBA code for converting hourly values in a year to average hourly values for each of the 24 hours (0–23 hr) of the day in 2020
The code used to convert hourly values (of a year) to average hourly values for each of the 24 hours of the day in 2020.
In the code for this section, first, I extracted the value for the last_row (8785) in the data table using
Cells(datetime.row, datetime.column).End(xlDown).row
to loop through each row for further processing.
And I used the HOUR() function in Excel to retrieve the corresponding hour in column A in each row using:
Hour(Cells(row, datetime.column).column).Value
The complete code for the purpose of this section is given in the snippet below:
Option Base 1
Sub GetHourlyAverage()‘defining an array for 4 strings
Dim columns(4) As String
columns(1) = “B”
columns(2) = “C”
columns(3) = “D”
columns(4) = “E”
'Definining mnth because Month is a function
Dim mnth As Integer
Dim row As Integer
Dim sum As Double
Dim num_hours As Double
Dim wb As Workbook
Dim ws As Worksheet
Dim datetime As Range
Dim last_row As Integer
Set wb = ThisWorkbook
Set ws = ThisWorkbook.Sheets("Sheet1")
Set datetime = ws.Range("datetime")
last_row = Cells(datetime.row, datetime.column).End(xlDown).row
Debug.Print datetime.Value
Debug.Print "Row: " & datetime.row & " Column: " & datetime.column
Debug.Print "Last row: " & last_row
'Loop through column for each city
For Each column In columns
'Loop through each hour of the day
For hr = 0 To 23
sum = 0
num_hours = 0
'Loop through each row
For row = datetime.row + 1 To last_row
If Hour(Cells(row, datetime.column).Value) = hr Then
Range(column & row).Interior.Color = RGB(255, 255, 0)
num_hours = num_hours + 1
sum = sum + Range(column & row).Value
End If
Next row
Range(column & hr + 2).Offset(0, 14).Value = sum / num_hours
Next hr
Next column
End Sub
In the above code snippet, Debug.Print command is used to print the intermediate results in the intermediate window in the VBA developer space as shown below:
2. Time Series Resampling using Pandas
The pandas library in Python provides an in-built method for time series resampling using df.resample() and passing the rule for resampling. For example, “M” is for monthly, “W” is for weekly, “Q” is for quarterly, “D” is for daily, “B” is for Business Day, etc. The complete set of rules available for resampling to different frequency levels can be found here.
A pre-requisite for time series resampling is that the dataframe index needs to be converted into a datetime type using pd.to_datetime().
Any built-in method available via Groupby is available as a method of the returned object of df.resample(), including min(), max(), mean(), median(), std(), first(), last(), ohlc(), and sem(). In this post, I am simply assessing the mean values of the solar irradiance.
In the Python code below, I allow the user to enter the frequency that they want to return and display for the solar irradiance values. The options include Original, Monthly average, Daily average, Weekly average, Quarterly average, All of the above, and Hourly average (for each of the 24 hours within a day).
import pandas as pd
import matplotlib.pyplot as plt
import os
import sys#Enter os system to current working directory
os.chdir(sys.path[0])
file = "solar_irradiance.xlsm"
#read all rows and first 5 columns
df = pd.read_excel(file).iloc[:, :5]
df["Datetime"] = pd.to_datetime(df["Datetime"])
df.set_index(["Datetime"], inplace = True)
frequency = input("Enter the frequency you want to display? \n1. Original \n2. Monthly average\n3. Daily average \n4. Weekly average\n 5.Quarterly average \n 6.All of the above \n 7. Hourly average \n? ")
if frequency == "Original":
print (df)
df.plot()
plt.title("Original solar irradiance in 2020")
plt.ylabel("Wh/m$^2$")
plt.legend()
plt.show()
elif frequency == "Monthly average":
print (df.resample(rule = "M").mean())
df.resample(rule = "M").mean().plot()
plt.title("Monthly average solar irradiance in 2022")
plt.ylabel("Wh/m$^2$")
plt.legend()
plt.show()
elif frequency == "Daily average":
print (df.resample(rule = "D").mean())
df.resample(rule = "D").mean().plot()
plt.title("Daily average solar irradiance in 2022")
plt.ylabel("Wh/m$^2$")
plt.show()
elif frequency == "Weekly average":
print (df.resample(rule = "W").mean())
df.resample(rule = "W").mean().plot()
plt.title("Weekly average solar irradiance in 2022")
plt.ylabel("Wh/m$^2$")
plt.legend()
plt.show()
elif frequency == "Quarterly average":
print (df.resample(rule = "Q").mean())
df.resample(rule = "Q").mean().plot()
plt.title("Quarterly average solar irradiance in 2022")
plt.ylabel("Wh/m$^2$")
plt.legend()
plt.show()
elif frequency == "All of the above":
fig, axs = plt.subplots(2, 2, figsize = (20, 10), sharex = True, sharey = True)
df.resample(rule = "D").mean().plot(ax = axs[0, 0])
axs[0, 0].set_title("Daily mean")
axs[0, 0].set_ylabel("Wh/m$^2$")
df.resample(rule = "W").mean().plot(ax = axs[0, 1])
axs[0, 1].set_title("Weekly mean")
df.resample(rule = "M").mean().plot(ax = axs[1, 0])
axs[1, 0].set_title("Monthly mean")
axs[1, 0].set_ylabel("Wh/m$^2$")
df.resample(rule = "Q").mean().plot(ax = axs[1, 1])
axs[1, 1].set_title("Quarterly mean")fig.suptitle("Mean solar irradiance in four locations converted to different temporal frequencies")
plt.show()
elif frequency == "Hourly average":
#average value in each hour within 24 hours of a day
print (df.groupby(df.index.hour).mean())
df.groupby(df.index.hour).mean().plot()
plt.title("Hourly average solar irradiance in 2022")
plt.ylabel("Wh/m$^2$")
plt.legend()
plt.show()
else:
print ("The frequency you entered is incorrect.")
This script can be run by going to the terminal/command prompt and typing python -m python_script.py if one is the same path as the script file. To abrubt the run, one can type Ctrl+C.
3. Running Python script via Excel VBA
The above Python script can also be run via Excel VBA. For this purpose, I saved the above script as python_script.py file.
The complete subroutine written in VBA to run the Python script is given in code snippet below.
Sub RunPythonScript()
Dim objShell As Object
Dim PythonExePath As String, PythonScriptPath As String
ActiveWorkbook.Save'Enter into the path of given workbook
ChDir Application.ThisWorkbook.Path
Set objShell = VBA.CreateObject(“Wscript.Shell”)
'Enter into the path of given workbook
ChDir Application.ThisWorkbook.Path
Set objShell = VBA.CreateObject("Wscript.Shell")
'Goto cmd. Type where python to get this path. Note that there are three quotes below.
' The hash symbol # below needs to be filled with the path in your system.
PythonExePath = """C:\Users\#######################\python.exe"""
'Get the path of the file.
PythonScriptPath = Application.ThisWorkbook.Path & "\python_script.py"
objShell.Run PythonExePath & PythonScriptPath
End Sub
First, I declared objShell variable to refer to the address of an object (Wscript.shell). This object allows to access the Windows functionality to run the external program (here, Python script).
The PythonExePath refers to the path of the Python application in the computer system. In a Windows system, this path can be found by typing where python in the Windows command prompt.
The path of the python script is defined as string in PythonScriptPath. Note that this path should not have space for the script to run
Finally, the following line is used to run the Python script using the Python application via the Excel VBA interface.
objShell.Run PythonExePath & PythonScriptPath
Output
I assigned the above sub-routine/macro (command written using VBA language) to a button in Excel file as shown below:
Clicking the button runs the Python script as shown below and asks the user to enter the frequency the user wants to display output as:
Upon selecting “All of the above”, I get the output plot for the daily, weekly, monthly, and quarterly average values of solar irradiance in the four cities in 2020. It is observed that as the time resolution increases from right to left and top to bottom, the line/curve becomes smoother as the variabilities in lower time resolutions are averaged out.
Conclusion
In this post, I presented three techniques for time series resampling of hourly solar irradiance data for four cities in 2020:
- Using Excel VBA
- Using pandas in Python
- Running Python script via Excel VBA interface
Excel VBA can be very handy while working with data in Excel because it allows us to perform various operations and have direct interactions with data in different sheets in the same or different Excel file where the sub-routines/macros are written. Also, by writing macros and assigning them to interactive buttons/userforms or other widgets, it is easy to share Excel file with other users to whom only the functionalities matter and not what happens with the code in the backend.
One of the main advantages of Python is that there are packages with several in-built functionalities, which makes writing a separate code for routine tasks redundant. In this context, combining the strengths of Excel VBA and Python can be very advantageous. This is depicted by the way I ran the Python script for time series resampling by clicking a button on an Excel file, which is associated with a few simple lines of VBA code.
The Excel file including the macros, and the Python script are available in the script folder within this repository. Thank you for reading!
A comprehensive assessment of time series resampling of solar irradiance using VBA, using Python, and using Python via Excel VBA
Recently, I have been working with Excel-based interface and models frequently. During this time, I became acquainted with the Visual Basic for Application (VBA) for Office, which is a powerful programming language to extend Office applications. VBA is used to automate repetitive tasks, extend user interactions, and interact between different Office applications, making the routine tasks more efficient and effective.
With a decent programming background in Python, it took me relatively steep learning curve towards the beginning to learn different features and functionalities of VBA, which gradually turned less steep with time. I have realized that learning VBA is very useful since it interacts with Excel workbook directly and can automate the tasks on the workbook itself that otherwise would require coding in a different programming language. However, if it is possible to run a script in a different programming language (e.g., Python) within Excel VBA, it can be even more helpful in automating our routine tasks.
In this post, I will share my experience of using Excel VBA and Python to perform a simple task — time series resampling of solar irradiance data. Furthermore, I am going to present how a Python script can be run via Excel VBA to perform the same task. Let’s get started.
Data
The data used is hourly All Sky Surface Shortwave Downward Irradiance (ALLSKY_SFC_SW_DWN) for 2020 (UTC time zone) downloaded from NASA Power website for four cities: Chitwan (Nepal), New York (USA), Sydney (Australia), and Bonn (Germany). The data was accessed and downloaded using the API service of NASA Power with a Python script, which I intend to document in a different post.
Solar irradiance is the power per unit area (W/m2) obtained from the sun in the form of electromagnetic radiation in the wavelength range of the measuring instrument. Solar irradiance integrated over a time period gives the solar irradiation, referred to as solar insolation (Wh/m2).
According to the definition of NASA Power, the parameter used All Sky Surface Shortwave Downward Irradiance (ALLSKY_SFC_SW_DWN) refers to the total solar irradiance (direct plus diffused) incident on a horizontal plane at the surface of the earth under all sky conditions. An alternative term for total solar irradiance is Global Horizontal Irradiance (GHI). Since the values are hourly, its unit is Wh/m².
Data in Excel file
Since 2020 was a leap year, I obtained the hourly solar irradiance values for 8784 hours for the four cities. These values are placed in column B, C, D and E of the Excel file as shown below.
To get the values in column A in date format, the following formula was used in Excel (e.g., in cell A2):
=TEXT(DATE(2020,1,1)+ROW(A1)/24, “yyyy-mm-dd hh:mm:ss”)
ROW(A1)/24 is used to get the value of hour within a day (0–23 hr).
Also, I named cell A1 as datetime.
Plotting the original hourly solar irradiance data for 2020 for the four cities in Excel looks as follows:
Time series resampling refers to converting the frequency level of time series data. In simple words, resampling is a time-based groupby, followed by a reduction method on each of the groups. The data must have a datetime-like index for this purpose.
1. Time Series Resampling using Excel VBA
There is no default function in Excel for time series resampling. Therefore, I wrote a couple of subroutines to get monthly and hourly average values as described in the sections a and b respectively below.
a. VBA code for converting hourly values to average monthly frequency
This section describes the code snippet (below) used to convert hourly value to average monthly value in VBA.
An array is a group of variables in VBA. The default lower bound of an array element in VBA is 0. Mentioning Option Base 1 on the top of the sub-routine changes the lower bound of array element to 1. I defined an array called columns(4) as a group of strings containing 4 variables. And I passed the strings B, C, D and E within this array.
Cell A1 in Excel is defined as a named cell datetime. To refer to this cell in VBA, I declared datetime as a range and assigned it to the range with same name in Excel.
To refer to the month from the datetime column in Excel, I used the MONTH() function and assigned it to an integer variable called mnth. To loop through hourly values in each row, I declared another integer called row. Finally, I declared sum and num_hours to calculate the monthly average values.
'Use Option Base 1 before this subroutine if you want to start the list from 1 instead of 0.
'https://excelchamps.com/vba/arrays/
Option Base 1
Sub GetMonthlyAverage()'defining an array for 4 strings
Dim columns(4) As String
columns(1) = "B"
columns(2) = "C"
columns(3) = "D"
columns(4) = "E"
'Refer to cell A1
Dim datetime As Range
Set datetime = Range("datetime")
'Definining mnth because Month is a function
Dim mnth As Integer
Dim row As Integer
Dim sum As Double
Dim num_hours As Double
Next, I created a for-loop to iterate through the columns for values for each city. Within this for-loop, there are two other nested loops to iterate through each month and hour of the year, respectively. The sum aggregates the hourly solar irradiance values for each month, and the num_hours aggregates the number of hours in each month. Finally, by dividing the sum with num_hours, monthly average values of solar irradiance are obtained for each month for each city.
'Loop through column for each city
For Each column In columns'Loop through each month of the year
For mnth = 1 To 12
sum = 0
num_hours = 0
'Loop through each row
For row = 2 To 8785
If MONTH(Cells(row, datetime.column)) = mnth Then
Range(column & row).Interior.Color = RGB(255, 255, 0)
num_hours = num_hours + 1
sum = sum + Range(column & row).Value
End If
Next row
Range(column & mnth).Offset(1, 7).Value = sum / num_hours
Next mnth
Next column
End Sub
As a note, Range(column & row).Interior.Color = RGB(255, 255, 0) highlights each cell with yellow color while iterating through the row (city) and column (mnth).
b. VBA code for converting hourly values in a year to average hourly values for each of the 24 hours (0–23 hr) of the day in 2020
The code used to convert hourly values (of a year) to average hourly values for each of the 24 hours of the day in 2020.
In the code for this section, first, I extracted the value for the last_row (8785) in the data table using
Cells(datetime.row, datetime.column).End(xlDown).row
to loop through each row for further processing.
And I used the HOUR() function in Excel to retrieve the corresponding hour in column A in each row using:
Hour(Cells(row, datetime.column).column).Value
The complete code for the purpose of this section is given in the snippet below:
Option Base 1
Sub GetHourlyAverage()‘defining an array for 4 strings
Dim columns(4) As String
columns(1) = “B”
columns(2) = “C”
columns(3) = “D”
columns(4) = “E”
'Definining mnth because Month is a function
Dim mnth As Integer
Dim row As Integer
Dim sum As Double
Dim num_hours As Double
Dim wb As Workbook
Dim ws As Worksheet
Dim datetime As Range
Dim last_row As Integer
Set wb = ThisWorkbook
Set ws = ThisWorkbook.Sheets("Sheet1")
Set datetime = ws.Range("datetime")
last_row = Cells(datetime.row, datetime.column).End(xlDown).row
Debug.Print datetime.Value
Debug.Print "Row: " & datetime.row & " Column: " & datetime.column
Debug.Print "Last row: " & last_row
'Loop through column for each city
For Each column In columns
'Loop through each hour of the day
For hr = 0 To 23
sum = 0
num_hours = 0
'Loop through each row
For row = datetime.row + 1 To last_row
If Hour(Cells(row, datetime.column).Value) = hr Then
Range(column & row).Interior.Color = RGB(255, 255, 0)
num_hours = num_hours + 1
sum = sum + Range(column & row).Value
End If
Next row
Range(column & hr + 2).Offset(0, 14).Value = sum / num_hours
Next hr
Next column
End Sub
In the above code snippet, Debug.Print command is used to print the intermediate results in the intermediate window in the VBA developer space as shown below:
2. Time Series Resampling using Pandas
The pandas library in Python provides an in-built method for time series resampling using df.resample() and passing the rule for resampling. For example, “M” is for monthly, “W” is for weekly, “Q” is for quarterly, “D” is for daily, “B” is for Business Day, etc. The complete set of rules available for resampling to different frequency levels can be found here.
A pre-requisite for time series resampling is that the dataframe index needs to be converted into a datetime type using pd.to_datetime().
Any built-in method available via Groupby is available as a method of the returned object of df.resample(), including min(), max(), mean(), median(), std(), first(), last(), ohlc(), and sem(). In this post, I am simply assessing the mean values of the solar irradiance.
In the Python code below, I allow the user to enter the frequency that they want to return and display for the solar irradiance values. The options include Original, Monthly average, Daily average, Weekly average, Quarterly average, All of the above, and Hourly average (for each of the 24 hours within a day).
import pandas as pd
import matplotlib.pyplot as plt
import os
import sys#Enter os system to current working directory
os.chdir(sys.path[0])
file = "solar_irradiance.xlsm"
#read all rows and first 5 columns
df = pd.read_excel(file).iloc[:, :5]
df["Datetime"] = pd.to_datetime(df["Datetime"])
df.set_index(["Datetime"], inplace = True)
frequency = input("Enter the frequency you want to display? \n1. Original \n2. Monthly average\n3. Daily average \n4. Weekly average\n 5.Quarterly average \n 6.All of the above \n 7. Hourly average \n? ")
if frequency == "Original":
print (df)
df.plot()
plt.title("Original solar irradiance in 2020")
plt.ylabel("Wh/m$^2$")
plt.legend()
plt.show()
elif frequency == "Monthly average":
print (df.resample(rule = "M").mean())
df.resample(rule = "M").mean().plot()
plt.title("Monthly average solar irradiance in 2022")
plt.ylabel("Wh/m$^2$")
plt.legend()
plt.show()
elif frequency == "Daily average":
print (df.resample(rule = "D").mean())
df.resample(rule = "D").mean().plot()
plt.title("Daily average solar irradiance in 2022")
plt.ylabel("Wh/m$^2$")
plt.show()
elif frequency == "Weekly average":
print (df.resample(rule = "W").mean())
df.resample(rule = "W").mean().plot()
plt.title("Weekly average solar irradiance in 2022")
plt.ylabel("Wh/m$^2$")
plt.legend()
plt.show()
elif frequency == "Quarterly average":
print (df.resample(rule = "Q").mean())
df.resample(rule = "Q").mean().plot()
plt.title("Quarterly average solar irradiance in 2022")
plt.ylabel("Wh/m$^2$")
plt.legend()
plt.show()
elif frequency == "All of the above":
fig, axs = plt.subplots(2, 2, figsize = (20, 10), sharex = True, sharey = True)
df.resample(rule = "D").mean().plot(ax = axs[0, 0])
axs[0, 0].set_title("Daily mean")
axs[0, 0].set_ylabel("Wh/m$^2$")
df.resample(rule = "W").mean().plot(ax = axs[0, 1])
axs[0, 1].set_title("Weekly mean")
df.resample(rule = "M").mean().plot(ax = axs[1, 0])
axs[1, 0].set_title("Monthly mean")
axs[1, 0].set_ylabel("Wh/m$^2$")
df.resample(rule = "Q").mean().plot(ax = axs[1, 1])
axs[1, 1].set_title("Quarterly mean")fig.suptitle("Mean solar irradiance in four locations converted to different temporal frequencies")
plt.show()
elif frequency == "Hourly average":
#average value in each hour within 24 hours of a day
print (df.groupby(df.index.hour).mean())
df.groupby(df.index.hour).mean().plot()
plt.title("Hourly average solar irradiance in 2022")
plt.ylabel("Wh/m$^2$")
plt.legend()
plt.show()
else:
print ("The frequency you entered is incorrect.")
This script can be run by going to the terminal/command prompt and typing python -m python_script.py if one is the same path as the script file. To abrubt the run, one can type Ctrl+C.
3. Running Python script via Excel VBA
The above Python script can also be run via Excel VBA. For this purpose, I saved the above script as python_script.py file.
The complete subroutine written in VBA to run the Python script is given in code snippet below.
Sub RunPythonScript()
Dim objShell As Object
Dim PythonExePath As String, PythonScriptPath As String
ActiveWorkbook.Save'Enter into the path of given workbook
ChDir Application.ThisWorkbook.Path
Set objShell = VBA.CreateObject(“Wscript.Shell”)
'Enter into the path of given workbook
ChDir Application.ThisWorkbook.Path
Set objShell = VBA.CreateObject("Wscript.Shell")
'Goto cmd. Type where python to get this path. Note that there are three quotes below.
' The hash symbol # below needs to be filled with the path in your system.
PythonExePath = """C:\Users\#######################\python.exe"""
'Get the path of the file.
PythonScriptPath = Application.ThisWorkbook.Path & "\python_script.py"
objShell.Run PythonExePath & PythonScriptPath
End Sub
First, I declared objShell variable to refer to the address of an object (Wscript.shell). This object allows to access the Windows functionality to run the external program (here, Python script).
The PythonExePath refers to the path of the Python application in the computer system. In a Windows system, this path can be found by typing where python in the Windows command prompt.
The path of the python script is defined as string in PythonScriptPath. Note that this path should not have space for the script to run
Finally, the following line is used to run the Python script using the Python application via the Excel VBA interface.
objShell.Run PythonExePath & PythonScriptPath
Output
I assigned the above sub-routine/macro (command written using VBA language) to a button in Excel file as shown below:
Clicking the button runs the Python script as shown below and asks the user to enter the frequency the user wants to display output as:
Upon selecting “All of the above”, I get the output plot for the daily, weekly, monthly, and quarterly average values of solar irradiance in the four cities in 2020. It is observed that as the time resolution increases from right to left and top to bottom, the line/curve becomes smoother as the variabilities in lower time resolutions are averaged out.
Conclusion
In this post, I presented three techniques for time series resampling of hourly solar irradiance data for four cities in 2020:
- Using Excel VBA
- Using pandas in Python
- Running Python script via Excel VBA interface
Excel VBA can be very handy while working with data in Excel because it allows us to perform various operations and have direct interactions with data in different sheets in the same or different Excel file where the sub-routines/macros are written. Also, by writing macros and assigning them to interactive buttons/userforms or other widgets, it is easy to share Excel file with other users to whom only the functionalities matter and not what happens with the code in the backend.
One of the main advantages of Python is that there are packages with several in-built functionalities, which makes writing a separate code for routine tasks redundant. In this context, combining the strengths of Excel VBA and Python can be very advantageous. This is depicted by the way I ran the Python script for time series resampling by clicking a button on an Excel file, which is associated with a few simple lines of VBA code.
The Excel file including the macros, and the Python script are available in the script folder within this repository. Thank you for reading!
Denial of responsibility! Techno Blender is an automatic aggregator of the all world’s media. In each content, the hyperlink to the primary source is specified. All trademarks belong to their rightful owners, all materials to their authors. If you are the owner of the content and do not want us to publish your materials, please contact us by email – [email protected]. The content will be deleted within 24 hours.