specific dates percentage change

Dear all,

is there a way to get various month end price percentage change with respect to the opening price of the year? I tested the below but unable to do so. So far I am able to get the DataFrame of data but unable to retrieve the value within to calculate percentage change.

jan_start_p = get_pricing('SPY', start_date='2018-01-02', end_date='2018-01-02', symbol_reference_date=None,
frequency='daily', fields=None, handle_missing='raise', start_offset=0)

jan_open = jan_start_p['open_price']

end_jan_p = get_pricing('SPY', start_date='2018-01-31', end_date='2018-01-31', symbol_reference_date=None,
frequency='daily', fields=None, handle_missing='raise', start_offset=0)
end_jan = end_jan_p['close_price']

end_jan_change = (end_jan / jan_open - 1)*100
end_jan_change

output: 2018-01-02 00:00:00+00:00 NaN
2018-01-31 00:00:00+00:00 NaN
dtype: float64

Appreciate your advice. Thank you.

Regards,
MH

Pandas has a very powerful method for resampling data into various timeframes. Specifically, one can get the year start and month end data with the following code

# Use the resample method to get year start and month end prices  
year_start_prices = daily_prices.resample('AS').first()  
month_end_prices = daily_prices.resample('M').last()

Now, we really just want to do the following

year_to_date_gain = (month_end_prices / year_start_prices) - 1.0

However, this doesn't work as expected. The problem is pandas being smart. Too smart at times. In doing this math it tries to match up the dates. It tries to match a month end date with a year start date. They don't match so we get a bunch of nans for a result. The solution is our ubiquitous resample method again. It can be used to both downsample data (eg get yearly data from daily data) or upsample data (get monthly data from yearly data). This latter is what we want. Like this

# Use the resample method to upsample our yearly to monthly data. Forward fill the data.  
monthly_year_start_prices = year_start_prices.resample('M').ffill()

Since we are upsampling we need to specify how to fill in the dates with no data. In this case we want to forward fill the data. The resample method creates the same index dates as our monthly data. Now when we do our math from above and the dates match and we get the year to date returns we wanted.

year_to_date_gain = (month_end_prices / monthly_year_start_prices) - 1.0

See the attached notebook.

If anyone is interested, there is a complete list of the offset aliases (eg "M" and "AS" used above) in the pandas docs https://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.html#offset-aliases. Not all of them are implemented in version 0.18 but most are.