I have several excel file that contains the following values for some popular financial instruments:
MM/DD/YY | HH:MM | Open | High | Low | Close | Volume
This is minute data going back to January 1st 2010. What I would like to be able to do is manipulate this data in a variety of ways:
1) verify that all minute values are represented. If a minute's values are missing I would like to fill in the blank with the close of the previous bar for all missing minutes. (This can be a little tricky with holidays and some markets close at the :15 minute mark of the hour)
2) extract the time and value for the Open, High, Low and Close for each day while summing the volume. (Ideally I would be able to enter a time range so that I can look at day session only versus 24 hour data)
3) I would like to be able to generate a plot of any day I like (or multiple days) that not only plots the OHLC values but also will plot a cumulative volume histogram. I would like to be able to choose a time increment as well.
I don't know anything but formula basics for excel. I'm not looking for someone to spoon feed me how to do this but I would like help creating a plan of attack. Do I need to use VBA? What is the most efficient order of operations? Examples of similar files. Anything would be appreciated.
Bookmarks