Resampling Time Series

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

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 Offset

Frequency String

Description

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

'BQ

business 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)

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)

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 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

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:

Reference : https://codingandfun.com

PreviousBuilding Investment AI for fintechNextPython for Finance (Series)

Last updated