Collapsing Daily Data

Click to Download Workbook: Converting Daily Prices

One of the perks of Yahoo Finance was the ability to select different frequencies or periodicities of historical time series data. What I mean by this is that you could select monthly and weekly prices. Albeit, this still can be done, it cannot be returned via the Yahoo API since it is largely defunct. Google Finance and most other free APIs do not offer this feature. There are some simple workarounds we can implement within Excel. Note with programming languages such as R  many packages take care of this dilemma for you with simple functions.

We could do this with arrays in visual basic, but personally, I would rather display a higher level overview of what has to go on in order to accomplish this. First things first, download some daily price data for a company of your choosing.

Monthly Separation:

Yahoo Finance takes the last day of each month and pastes the first day of the month as the date over it. For instance, a price of $1,100 on 11/30/2014 would suffice as the monthly value in the time series. Yahoo would change the output to 11/1/2014 with a price of $1,100. In order to do accomplish the same task, we must create three new columns. One for the month of the date, day of the date, and year of the date.

We can then remove the duplicates in the time series data to extract each year and unique month. To do this click on the data tab and then remove duplicates. A menu will pop up displaying which duplicates you would like to remove based on the column headers. Click Month and Year.

This is the general procedure for filtering the data. The next portion will highlight the Visual Basic code that indexes the proper dates and prices.

Weekly Separation:

About the author

programmingforfinance

Hi, I'm Frank. I have a passion for coding and extend it primarily within the realm of Finance.

View all posts

Leave a Reply

Your email address will not be published. Required fields are marked *