TKH is using the new STOCKHISTORY function in Excel. He is looking for stock prices from 7, 14, 30, and 60 days ago. When you are asking for a single day, the STOCKHISTORY function returns a #VALUE error if the market is closed on the desired day.
This video shows how to use the old WORKDAY function to make sure your date never ends on a holiday or weekend.
This video shows how to use the old WORKDAY function to make sure your date never ends on a holiday or weekend.
Transcript of the video:
Run STOCKHISTORY for a day that falls after a weekend or holiday.
This is our episode 2468.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen. Today's question from TKH.
Trying to track previous stock prices by either 7, 14, 30, or 90 days from today's date.
It just so happens that I happen to be running this on Sunday the 13th.
And when I tried to do the STOCKHISTORY for Microsoft MSFT for today minus seven, that evaluates to Sunday, February 6th.
And there was no prices that day, so it returns a #VALUE! error, right?
So the question is how can we go today minus seven today minus 30 today minus 90 and make sure that we never end up on a Saturday, Sunday or a market holiday?
And it's a great old function that I haven't used in a while called the WORKDAY function.
The WORKDAY function is designed to return Monday through Friday dates.
You can also optionally have it leave holidays out right? So starting on the inside: TODAY() minus 7.
TODAY() minus seven will get me back to Sunday, February 6th.
From there I want to go forward one day, this 1 right here says I'm going forward one day.
But, unfortunately on a Monday that will end up getting me Tuesday's result.
So I have to start from today minus seven, and go back 1 day and then go forward 1 day.
I tried it without this and it was ending up on Sunday.
WORKDAY doesn't advance if that second item there is not positive.
Right, so you have to, for the “number of days” you have to put a one in to get it to actually move up to the next day. Alright, so right there we would be done.
There's an optional argument here called holidays.
Eventually you're going to run into like 30 days after Christmas or 30 days after New Years or something like that.
You're going to have a market holiday.
So just somewhere - anywhere - in your spreadsheet, put all of the market holidays. I had to Google this and type them in here.
And so for me, it's E8:E28.
And that optional holidays argument, makes sure that if 7, 14, 30, or 90 days ago falls on a holiday, it will go forward one day.
OK, so the solution then.
I put the numbers 7, 14, 30, an d90 down the left hand side here. Stock ticker up in A1 is MSFT.
I put the headings in.
The STOCKHISTORY will give you headings, but then it gives you 2 rows all the time, so I figure it's easier just to put the headings in myself and then let STOCKHISTORY Just return the numbers.
So taking a look at this STOCKHISTORY of Microsoft, actually, that should be A1, shouldn't it?
And then WORKDAY from TODAY, minus the date over in A4, which is 7, 14, 30, or 90. I'll go back one day from that.
Go forward one work day from that.
And oh, by the way, ignore these holidays right here This comma comma?
That's for the optional end date since you only want the price for one date, you leave that blank.
Interval is daily. And then what fields do we want?
You can put these in any order and you don't have to put them all.
So zero is the date, 1 is closed, 2 is open, 3 is high, 4 is low, and 5 is volume, like that.
And then just copy it down to the next three rows and you should be good to go.
We should be able to come up here and put in Coca Cola – KO - and it will retrieve those dates.
Should work even if there was a market holiday, whether it falls on a Saturday, Sunday, Monday or anything.
If you like these videos, please down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below. Alright hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you love Excel, check out my new courses on the Retrieve platform.
They are video courses, but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages. It's a super fast way to learn.
This is our episode 2468.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen. Today's question from TKH.
Trying to track previous stock prices by either 7, 14, 30, or 90 days from today's date.
It just so happens that I happen to be running this on Sunday the 13th.
And when I tried to do the STOCKHISTORY for Microsoft MSFT for today minus seven, that evaluates to Sunday, February 6th.
And there was no prices that day, so it returns a #VALUE! error, right?
So the question is how can we go today minus seven today minus 30 today minus 90 and make sure that we never end up on a Saturday, Sunday or a market holiday?
And it's a great old function that I haven't used in a while called the WORKDAY function.
The WORKDAY function is designed to return Monday through Friday dates.
You can also optionally have it leave holidays out right? So starting on the inside: TODAY() minus 7.
TODAY() minus seven will get me back to Sunday, February 6th.
From there I want to go forward one day, this 1 right here says I'm going forward one day.
But, unfortunately on a Monday that will end up getting me Tuesday's result.
So I have to start from today minus seven, and go back 1 day and then go forward 1 day.
I tried it without this and it was ending up on Sunday.
WORKDAY doesn't advance if that second item there is not positive.
Right, so you have to, for the “number of days” you have to put a one in to get it to actually move up to the next day. Alright, so right there we would be done.
There's an optional argument here called holidays.
Eventually you're going to run into like 30 days after Christmas or 30 days after New Years or something like that.
You're going to have a market holiday.
So just somewhere - anywhere - in your spreadsheet, put all of the market holidays. I had to Google this and type them in here.
And so for me, it's E8:E28.
And that optional holidays argument, makes sure that if 7, 14, 30, or 90 days ago falls on a holiday, it will go forward one day.
OK, so the solution then.
I put the numbers 7, 14, 30, an d90 down the left hand side here. Stock ticker up in A1 is MSFT.
I put the headings in.
The STOCKHISTORY will give you headings, but then it gives you 2 rows all the time, so I figure it's easier just to put the headings in myself and then let STOCKHISTORY Just return the numbers.
So taking a look at this STOCKHISTORY of Microsoft, actually, that should be A1, shouldn't it?
And then WORKDAY from TODAY, minus the date over in A4, which is 7, 14, 30, or 90. I'll go back one day from that.
Go forward one work day from that.
And oh, by the way, ignore these holidays right here This comma comma?
That's for the optional end date since you only want the price for one date, you leave that blank.
Interval is daily. And then what fields do we want?
You can put these in any order and you don't have to put them all.
So zero is the date, 1 is closed, 2 is open, 3 is high, 4 is low, and 5 is volume, like that.
And then just copy it down to the next three rows and you should be good to go.
We should be able to come up here and put in Coca Cola – KO - and it will retrieve those dates.
Should work even if there was a market holiday, whether it falls on a Saturday, Sunday, Monday or anything.
If you like these videos, please down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below. Alright hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you love Excel, check out my new courses on the Retrieve platform.
They are video courses, but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages. It's a super fast way to learn.
