An awesome new function from Microsoft 365 debuted this afternoon for the beta channel. Load Daily, Weekly, or Monthly Stock Data into Excel using the new STOCKHISTORY function.
This video discusses the new #BUSY! error in Excel.
Learn about the awesome new FNV - Formatted Number Value and how XLOOKUP, IF, INDEX, and CHOOSE) can lookup a formatted number.
Also - how to do Exchange rates.
Table of Contents
(0:00) New Names for Microsoft 365 and Beta channel
(0:23) STOCKHISTORY function syntax
(0:49) Example using Zoom Video Stock
(1:50) #BUSY! error in Excel
(2:27) Updates a few hours after market close
(3:08) Forcing it to a certain stock exchange
(3:30) Returning historical exchanges rates in Excel
(3:36) NEW! Formatted Number Values (FNV) debut in Excel
(4:20) Need to be on beta channel of Microsoft 365
This video discusses the new #BUSY! error in Excel.
Learn about the awesome new FNV - Formatted Number Value and how XLOOKUP, IF, INDEX, and CHOOSE) can lookup a formatted number.
Also - how to do Exchange rates.
Table of Contents
(0:00) New Names for Microsoft 365 and Beta channel
(0:23) STOCKHISTORY function syntax
(0:49) Example using Zoom Video Stock
(1:50) #BUSY! error in Excel
(2:27) Updates a few hours after market close
(3:08) Forcing it to a certain stock exchange
(3:30) Returning historical exchanges rates in Excel
(3:36) NEW! Formatted Number Values (FNV) debut in Excel
(4:20) Need to be on beta channel of Microsoft 365
Transcript of the video:
Learn Excel from MrExcel podcast episode 2334. The StockHistory function.
Hey, welcome back to the MrExcel NetCast. I'm Bill Jelen.
Well something really exciting today, June 10th 2020.
It debuted for what used to be called Office Insiders is now called the Beta Channel of what used to be called Office 365 and is now Microsoft 365.
It is a great new function and array formula that returns stock history. Check out this syntax here.
Equals STOCKHISTORY. The stock symbol. Start date. Optionally the end date. If you don't put an end date in they assume today.
Oh, by the way, this is only end of day trading. It is not intraday. Use the Stocks Data Type if you need intraday. The interval is daily, weekly ,or monthly.
Whether or not you want the headers and then what you want to return. We'll take a look at those.
All right. So here let's let's try one of these.
So I have a symbol up there for Zoom Technologies. =STOCKHISTORY. What stock do I want? . I want the stock in A1. Start date?
We can either put in a date like this "1-1-2019" in quotes, or you can point to a cell that has a date.
For the end date I'm going to put in TODAY(), even though that is assumed. And then interval. Do we want daily, weekly, or monthly? I'll go with monthly.
For headers: zero is no header. You might want to do that if you're sorting. One is Show Header.
Two is show header and also the the stock symbol. We already have that up there.
So I'll just go with show header. That seems to work out. Then check this out.
We have choices here six different numbers: Date, Closed, Open, High, Low, and Volume.
You can choose the sequence that you want those in.
So, in my particular case I actually want date first and then close and maybe volume.
So something like that, I press ENTER.
I get this brand new error we've never had: hashtag BUSY exclamation point. That means they're out collecting this data. It is super frustrating.
You can sit here and stare at this forever and it never comes back.
Eventually, you're wonder what's taking so long? Then finally get frustrated and go work on some other worksheet.
As you start to work on other worksheets, you enter something. This forces a calculation. Once you force the calculation then the STOCKHISTORY displays.
I don't know if that's by design or if that's a bug. I am not really sure.
Check this out it is super cool. As I mentioned, no intraday.
All right, this is at the end of the day. The market closed for today.
I will change this. Change this to see instead of monthly.
I'll change this to daily.
And again, you get the #BUSY!.
You just have to enter something somewhere and the answers appear. So today is late on June 10th.
The market is closed and we go down here and it's still not reporting the data for June 10th, right? It's already 6:43 PM.
Geez, the markets been closed for over two hours.
So it's some amount of time. Certainly by tomorrow morning, it will be there.
So just be aware that it's at the end of the day. All right, interesting little tips here. You can force an exchange.
So if I just put an MSFT, I'm gonna get Microsoft on the NASDAQ exchange.
But if I want it on the Swiss exchange I could ask this XSWX:MSFT. If you want to be really safe make it into a data type.
Here and then I can use a little formula of =E1.Exchange to see this is the Swiss exchange.
It also works with exchange rates.
And something is really cool. Array formulas never did when dynamic arrays were introduced.
It is something called an FNV Formatted Number Value. So check this out. If I'm converting USD to GBP.
I'm getting pounds in the result.
Four functions XLOOKUP, INDEX, IF, and CHOOSE will return the formatted value, which is really cool.
When I change this to USD to CAD for Canadian dollars, it changes back to a dollar sign. We are getting the dollar sign there.
That is awesome. What is happening there? That's formatted as general. It's not changing the number format.
It's something brand-new From Joe McDaid and his team.
All right, so I love this new STOCKHISTORY. You have to be on the Beta Channel or Office Insiders of the Microsoft 365.
Hey, if you like these tips, please down there below the video Subscribe and ring that bell. Feel free to post any questions or comments in the comments below.
My new book, MrExcel 2020 Seeing Excel Clearly clearly. I have to update this book for this great new stock history function.
Click that "i" in the top right hand corner for more information. I want to thank you for stopping by.
We'll see you next time for another Netcast from MrExcel.
Hey, welcome back to the MrExcel NetCast. I'm Bill Jelen.
Well something really exciting today, June 10th 2020.
It debuted for what used to be called Office Insiders is now called the Beta Channel of what used to be called Office 365 and is now Microsoft 365.
It is a great new function and array formula that returns stock history. Check out this syntax here.
Equals STOCKHISTORY. The stock symbol. Start date. Optionally the end date. If you don't put an end date in they assume today.
Oh, by the way, this is only end of day trading. It is not intraday. Use the Stocks Data Type if you need intraday. The interval is daily, weekly ,or monthly.
Whether or not you want the headers and then what you want to return. We'll take a look at those.
All right. So here let's let's try one of these.
So I have a symbol up there for Zoom Technologies. =STOCKHISTORY. What stock do I want? . I want the stock in A1. Start date?
We can either put in a date like this "1-1-2019" in quotes, or you can point to a cell that has a date.
For the end date I'm going to put in TODAY(), even though that is assumed. And then interval. Do we want daily, weekly, or monthly? I'll go with monthly.
For headers: zero is no header. You might want to do that if you're sorting. One is Show Header.
Two is show header and also the the stock symbol. We already have that up there.
So I'll just go with show header. That seems to work out. Then check this out.
We have choices here six different numbers: Date, Closed, Open, High, Low, and Volume.
You can choose the sequence that you want those in.
So, in my particular case I actually want date first and then close and maybe volume.
So something like that, I press ENTER.
I get this brand new error we've never had: hashtag BUSY exclamation point. That means they're out collecting this data. It is super frustrating.
You can sit here and stare at this forever and it never comes back.
Eventually, you're wonder what's taking so long? Then finally get frustrated and go work on some other worksheet.
As you start to work on other worksheets, you enter something. This forces a calculation. Once you force the calculation then the STOCKHISTORY displays.
I don't know if that's by design or if that's a bug. I am not really sure.
Check this out it is super cool. As I mentioned, no intraday.
All right, this is at the end of the day. The market closed for today.
I will change this. Change this to see instead of monthly.
I'll change this to daily.
And again, you get the #BUSY!.
You just have to enter something somewhere and the answers appear. So today is late on June 10th.
The market is closed and we go down here and it's still not reporting the data for June 10th, right? It's already 6:43 PM.
Geez, the markets been closed for over two hours.
So it's some amount of time. Certainly by tomorrow morning, it will be there.
So just be aware that it's at the end of the day. All right, interesting little tips here. You can force an exchange.
So if I just put an MSFT, I'm gonna get Microsoft on the NASDAQ exchange.
But if I want it on the Swiss exchange I could ask this XSWX:MSFT. If you want to be really safe make it into a data type.
Here and then I can use a little formula of =E1.Exchange to see this is the Swiss exchange.
It also works with exchange rates.
And something is really cool. Array formulas never did when dynamic arrays were introduced.
It is something called an FNV Formatted Number Value. So check this out. If I'm converting USD to GBP.
I'm getting pounds in the result.
Four functions XLOOKUP, INDEX, IF, and CHOOSE will return the formatted value, which is really cool.
When I change this to USD to CAD for Canadian dollars, it changes back to a dollar sign. We are getting the dollar sign there.
That is awesome. What is happening there? That's formatted as general. It's not changing the number format.
It's something brand-new From Joe McDaid and his team.
All right, so I love this new STOCKHISTORY. You have to be on the Beta Channel or Office Insiders of the Microsoft 365.
Hey, if you like these tips, please down there below the video Subscribe and ring that bell. Feel free to post any questions or comments in the comments below.
My new book, MrExcel 2020 Seeing Excel Clearly clearly. I have to update this book for this great new stock history function.
Click that "i" in the top right hand corner for more information. I want to thank you for stopping by.
We'll see you next time for another Netcast from MrExcel.
