I’ve already discussed the issues associated with working with dates in Excel. It’s much harder than it should be. Unfortunately, that is also true about working with time. In a way, I can understand that excel can’t always guess what we’re trying to do. It should be a bit better at it though. Yesterday, I got a question from a reader that a simple question. He needed to buy 1000 shares of Microsoft done evenly throughout the day. It’s more commonly known as a TWAP (time weighted average price). He was trying to easily see if the program had bought enough or perhaps too much at multiple points during the day.
I told him I’d build a simple spreadsheet but it proved a bit more difficult than expected. Why? It’s mostly about cell formatting. Here is what I built initially:
The first part is easy. My first task is finding the number of minutes between the start and end. I need that to determine what quantity needs to be executed depending on the time. I will use 2 functions to achieve this task. Basically, I need to translate the 6:30 into minutes.
How? I will add the number of hours x 60 to the number of minutes. Unfortunately, here is the result:
However, I then simply changed the format of that cell to numbers and got this:
So every minute I must execute the total qty/mins. In this case, it is 1000/390.
Then, I needed to determine for each time on the left how much time had lapsed since the start time. How? It’s a similar principle. For C5 I will use:
=MINUTE(B5)+HOUR(B5)*60-MINUTE($J$4)-HOUR($J$4)*60
However as you can see the result isn’t good:
Why? Because the time is not being treated correctly. So I will select the entire B column and right click for cell format, I then used this format:
I now have the number of minutes between that time and the start. I can now simply multiply that by the qty/minute and I’ll know how much I need to have executed:
As you can see, I also added the use of “Max” and “round”. The round is simply to avoid having decimals while I use the max because if I change my start time to 10:00 AM, I want to avoid having negative quantities.
You can of course download the spreadsheet here.
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
ebooks
I am not positive the place you’re getting your info,
however great topic. I must spend a while studying more or understanding more.
Thanks for fantastic information I used to be searching for this information for my mission.
via cash
Interest Rate
Managing Time In Excel | Experiments in Finance