While working with stock market data, sometime we would like to change our time window of reference. Generally daily prices are available at stock exchenges. Let us see how to conert daily prices into weekly and monthly prices.
You can download daily prices from NSE from this link. We will downoad daily prices for last 24 months. Here is the sample file with which we will work
15-06-2016-TO-14-06-2018HDFCBANKALLN
Please refer to below program to convert daily prices into weekly. Comments in the program will help you understand the logic behind each line.
Once you understand daily to weekly, only small modification is needed to convert this into monthly OHLC data. Here is the script
Here are the output files for your reference.
I wasted some time to find ‘Open Price’ for weekly and monthly data. I tried some complex pandas queries and then realized same can be achieved by simply using aggregate function and ‘Open Price‘:‘first.
Please do let me know your feedback.
It is unfortunately not 100% correctly. I receive sometimes week 1, but still with the previous year. It can occur when 31.12 is Monday. Then you have incorrect values for this particular row. I would suggest to use this approach:
https://stackoverflow.com/questions/34597926/converting-daily-stock-data-to-weekly-based-via-pandas-in-python
Agree… You gave a very good point of special case.
To resolve this issue, we can directly handle this specific special case like:
date_range_frame[‘week’] = date_range_frame[‘date’].dt.week
date_range_frame[‘year’] = date_range_frame[‘date’].dt.year
date_range_frame[‘month’] = date_range_frame[‘date’].dt.month
date_range_frame[‘quarter’] = date_range_frame[‘date’].dt.quarter
initialize week-year to year first
date_range_frame[‘week-year’] = date_range_frame[‘year’]
incease the week-year by 1 for the special case
date_range_frame.loc[(date_range_frame[‘month’] == 12) & (date_range_frame[‘week’] == 1), ‘week-year’] = date_range_frame[‘year’] + 1
It is Wonderful. It is Great! Excellent!