Back to Community
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

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.


2 responses

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 Not all of them are implemented in version 0.18 but most are.

Loading notebook preview...

The material on this website is provided for informational purposes only and does not constitute an offer to sell, a solicitation to buy, or a recommendation or endorsement for any security or strategy, nor does it constitute an offer to provide investment advisory services by Quantopian. In addition, the material offers no opinion with respect to the suitability of any security or specific investment. No information contained herein should be regarded as a suggestion to engage in or refrain from any investment-related course of action as none of Quantopian nor any of its affiliates is undertaking to provide investment advice, act as an adviser to any plan or entity subject to the Employee Retirement Income Security Act of 1974, as amended, individual retirement account or individual retirement annuity, or give advice in a fiduciary capacity with respect to the materials presented herein. If you are an individual retirement or other investor, contact your financial advisor or other fiduciary unrelated to Quantopian about whether any given investment idea, strategy, product or service described herein may be appropriate for your circumstances. All investments involve risk, including loss of principal. Quantopian makes no guarantees as to the accuracy or completeness of the views expressed in the website. The views are subject to change, and may have become unreliable for various reasons, including changes in market conditions or economic circumstances.

Thank you Dan! you are really great at this! Do you have a book on python published by yourself? Would love to get it if you do!

Best regards,