Resampling Time Series with Pandas – From Daily to Monthly NASDAQ Prices

During this post, we are going to learn how to resample time series data with Pandas. We will see how to resample stock related daily historical prices into different frequencies using Python and Pandas.

Resampling Time Series with Pandas
Photo by Burak K on Pexels.com

What is Frequency Resampling?

Resampling is simply to convert our time series data into different frequencies. The frequency conversion will depend on the requirements of our analysis.

Why is Resampling Important in Finance?

To use an easy example, imagine that we have 20 years of historical daily prices of the S&P500. Our boss has requested us to present the data with a monthly frequency instead of daily.

This is when resampling comes in handy. We can convert our time series data from daily to monthly frequencies very easily using Pandas.

How to Resample in Pandas

Resampling a time series in Pandas is super easy. Not only is easy, it is also very convenient. Pandas offers multiple resamples frequencies that we can select in order to resample our data series.

Below are some of the most common resample frequency methods that we have available. See the following link to find out all available frequencies:

Date OffsetFrequency StringDescription
BDay or BusinessDay'B'business day (weekday)
Week'W'one week, optionally anchored on a day of the week
MonthEnd'M'calendar month end
MonthBegin'MS'calendar month begin
BMonthEnd or BusinessMonthEnd'BM'business month end
BMonthBegin or BusinessMonthBegin'BMS'business month begin
SemiMonthEnd'SM'15th (or other day_of_month) and calendar month end
SemiMonthBegin'SMS'15th (or other day_of_month) and calendar month begin
QuarterEnd'Q'calendar quarter end
QuarterBegin'QS'calendar quarter begin
BQuarterEnd'BQbusiness quarter end
BQuarterBegin'BQS'business quarter begin
YearEnd'A'calendar year end
YearBegin'AS' or 'BYS'calendar year begin
BYearEnd'BA'business year end
BYearBegin'BAS'business year begin
Day'D'one absolute day
Hour'H'one hour
Pandas Time Series Resampling

Steps to resample data with Python and Pandas:

Those threes steps is all what we need to do. Let’s have a look at a practical example in Python to see how easy is to resample time series data using Pandas.

Retrieving NASDAQ Historical Prices

We will be using the NASDAQ index as an example. As in my previous posts, I retrieve all required financial data from the FinancialModelingPrep API. In this case, we will retrieve NASDAQ historical daily prices for the last few years.

Note that an API key is required in order to extract the data. You can get one for free (offering up to 250 API calls per month). If that is not enough, you can buy a yearly subscription for a little more than 100$. Readers of this blog can benefit from a 25% discount in all plans using the following discount link.

See below that we pass ^NDX as argument of the URL in order to get the NASDAQ prices. Check the API documentation to find out the symbol for other main indexes and ETFs.

import pandas as pd
import requests
demo = 'your api key'

#1 Get all historical daily prices for NASDAQ
ETFs = requests.get(f'https://financialmodelingprep.com/api/v3/historical-price-full/^NDX?apikey={demo}').json()
ETFs = ETFs['historical'] 
print(ETFs)

[{'adjClose': 10905.879883,
  'change': 19.51953,
  'changeOverTime': 0.00179,
  'changePercent': 0.179,
  'close': 10905.879883,
  'date': '2020-07-31',
  'high': 10908.339844,
  'label': 'July 31, 20',
  'low': 10707.400391,
  'open': 10886.360352,
  'unadjustedVolume': 4418390000.0,
  'volume': 4418390000.0,
  'vwap': 10840.54004},
 {'adjClose': 10715.509766,
  'change': 136.87988,
  'changeOverTime': 0.01294,..
...
...}]

Now, we have a Python list containing few years of daily prices. For better data manipulation, we transform the list into a Python dictionary and then convert the dictionary into a Pandas DataFrame. Finally, we reset the index:

#add each of the historical prices into a dictionary
hist_Prices= {}
for item in ETFs:
  date_Etf = item['date']
  hist_Prices[date_Etf] = item

ETFs= pd.DataFrame.from_dict(hist_Prices,orient='index')

ETFs.reset_index(inplace=True)
print(ETFs)
Pandas DataFrame NASDAQ Time Series
Pandas DataFrame NASDAQ Time Series

Resampling Data with Pandas

Until now, we manage to create a Pandas DataFrame. We are ready to apply the resampling method and convert our prices into the desired frequency. For this example, lets assume that we want to see the monthly and yearly NASDAQ historical prices:

Before we do that, we still need to do some data preparation in our Pandas DataFrame. For the resampling data to work, we need to convert dates into Pandas Data Types.

Then, we keep only two of the columns, date and adjClose to get rid of unnecessary data.

#convert date into datetime for later resampling purposes 
ETFs['date'] = ETFs.loc[:,'date'].astype('datetime64[ns]')

ETFs = ETFs[['date','adjClose']]
ETFs.set_index('date',inplace=True)
print(ETFs)
Resampling Times Series with Python
Resampling Times Series with Python

Finally, let’s resample our DataFrame. It is super easy. We can use the resample method and pass the resample frequency that we want to use. In below code, we resample the DataFrame into monthly and yearly frequencies.

Note, that Pandas will automatically calculate the mean of all values for each of the months, and show that result as the outcome in a new DataFrame:

#Resample dataframe in order to get monthly prices and yearly prices
etf_Monthly_Price = ETFs.resample('M').mean()
etf_Yearly_Price = ETFs.resample('Y').mean()
print(etf_Monthly_Price)
print(etf_Yearly_Price)
Resample Time Series Data into Months
Resample Time Series Data into Months
5065065Resample Time Series Data into Years

Is it not great? We have now resampled our data to show monthly and yearly NASDAQ historical prices as well. And all of that only using a line of Python code.

In the above example, we have taken the mean of all monthly and yearly values. That is the outcome shown in the adj Close column. But what if we would like to keep only the first value of the month?

That is super easy to achieve:

Simply use the same resample method and change the argument of it. For instance, MS argument lets Pandas knows that we want to take the first day of the month. We also use the method first, in order to keep the first value:

etf_Monthly_Price = ETFs.resample('MS').first()
etf_Yearly_Price = ETFs.resample('YS').first()
etf_Monthly_Price

Pandas Resample First Day of Month
Pandas Resample First Day of Month

Wrapping Up

In addition to take the first day or mean as the frequency of the resample, there are plenty of other frequencies available to us. You can use them as instructed in the Pandas Documentation.

In my next post, we will use resampling in order to compare the returns of two different investing strategies, Dollar-Cost Averaging versus Lump Sum investing.

Thanks for reading the blog! Learn more about Python for Finance in my blog:

Find the video tutorial version in the post below:

3 thoughts on “Resampling Time Series with Pandas – From Daily to Monthly NASDAQ Prices

  1. Good article, but you forgot the most important part. 😉

    Say I want to resample to Weekly, but with the same DataFrame structure, as is original daily data. To have ‘Open’, ‘High’, ‘Low’… but as weekly.

    Would you mind create a guide, how to do this, please?

  2. Thanks for the comment! You can simply add.
    Then it should work!

    etf_Monthly_Price = ETFs.resample(‘M’).mean()

Comments are closed.