Office 365 customers will soon be seeing a new Data Types gallery in the Data tab in Excel. Convert stock symbols to a Stock Data Type. You can easily return price, CEO, Volume, and other statistics.
Transcript of the video:
Learn Excel from MrExcel podcast episode 2250. Stock Data Types in Excel.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen and today we're talking about the stock data types.
Now, if you didn't see yesterday episode 2249 on geography data types in Excel, this is actually going to build on that.
And even if you don't care about stocks, you just care about geography, there's some cool new tips in here that will apply to both stocks and geography.
All right. Now today, I wanna be a little smarter than yesterday in that I have...
I'm gonna save the original version and then have this version and, of course, I made this into a table with Home > Format as Table.
There's some good benefits from using a table with this data.
So we're coming over to the data tab and you have Stocks and Geography again.
Mine's in Spanish - some bad crash up - and the rest of the ribbons are not in Spanish. All right.
Choose stocks and it goes out to the Internet and it converts everything. All right, now...
Geez, this is so annoying I just had IBM and now they have International Business Machines Corp.
Super annoying that that grew but I can't find any way to change that.
All right, now this is annoying. I was trying to get Sony SNL...
it's SNE, so I'm gonna come here I'm gonna type SNE and they will go out and it worked.
All right, so that's all great. Now, yesterday I showed you how we could show a card.
All right, so you come here and you click on this on show card or CRTL+SHFT+F5 and you can get all this data.
All right, so they have previous close, they have the open, high/low, 52 week high, 52 week low, volume, price to earnings ratio, the CEO.
All right, now if we want one of these like previous close.
Let's say just hover and this symbol appears and that data will show up in the grid.
If it wasn't a table, it only shows up in cell C2 but because this is a table, it gets copied down to all of the cells.
So great reason to use the table like that.
You can also come out here, I think I showed this yesterday, and choose one of these items or you can just come here. This is so cool.
And type price and if that's one of those elements, BAM, they will figure it out and add it in.
All right, now, while this is super awesome cool, the people who really trade stocks every day are gonna just hate this because there's no good way to get the last 10 prices or the last 20 prices or the price for the last 30 days.
All we have is yesterday's close and today's current price.
All right, so I already know this is gonna disappoint a lot of people and, you know, hey, it is what it is.
Up here you can learn about the data sources and they don't just support the US stock markets but they support a whole bunch of different stock prices here and it tells you what countries they support.
So check out all these countries, which exchanges they support, so if you're in one of these countries and I'll scroll through here, you will be able to see those various prices.
My title card today with the oranges I was actually gonna steal a scene from trading places, the a great movie with Dan Aykroyd and I was going to do frozen concentrated orange juice but unfortunately those are not working.
I can't put commodities over there, you know.
So it's not again it's not as wide as having a subscription to one of the big services but there are a lot of choices here.
And, you know, if you just have a simple little, you know, you have a portfolio and you know how many shares you have and you want to get the current stock price to see whether you're up or down today, that's gonna work great for that.
Hey, one thing here, this will not automatically get recalculated with the sheet.
If you want to refresh this you have to come out here, right click, data type and refresh or I suppose that refresh either way should force it to go out and get new prices.
All right, so this isn't designed to be constantly streaming new prices into the worksheet. You have to specifically ask the refresh.
You should also be able to come out here and Data > Refresh All.
All right, so you know just be cautious.
All right, now none of this is gonna work if someone has Excel 2016 or 2013 or even Excel 2019.
You have to be on office 365 to convert this you're going to copy CRT+C and then Home > Paste > Paste as Values, which will correctly convert all of these formulas to values.
These though, are still rich data types so to convert those make sure that you're on a cell that has the icon and then right-click and datatype and convert to text.
If you would have just converted the whole thing to text, well, then column B converts and all those formulas over on the right, turn into errors, right?
So, just really kind of annoying, the way that you have to do it in the correct sequence like I just did.
Watch the outtake if you want to see the the bad outcome of doing it the other way.
All right, so here we have the new Data types Stocks and Geography and these are just the first two.
I'm sure that more will be coming, you know, if you have a great idea for a new data type that you'd like to see in Excel, mention it down in the YouTube comments and I'll send it on to the Excel team.
They are actively looking for ideas of where to go next.
Great new functionality in Excel and again once this is built out, it should be relatively easy for them to add more.
All right, today we talked about Stock data types new in Excel for Office 356.
To download the workbook from today's video, visit the URL in the YouTube description.
Hey, 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 and today we're talking about the stock data types.
Now, if you didn't see yesterday episode 2249 on geography data types in Excel, this is actually going to build on that.
And even if you don't care about stocks, you just care about geography, there's some cool new tips in here that will apply to both stocks and geography.
All right. Now today, I wanna be a little smarter than yesterday in that I have...
I'm gonna save the original version and then have this version and, of course, I made this into a table with Home > Format as Table.
There's some good benefits from using a table with this data.
So we're coming over to the data tab and you have Stocks and Geography again.
Mine's in Spanish - some bad crash up - and the rest of the ribbons are not in Spanish. All right.
Choose stocks and it goes out to the Internet and it converts everything. All right, now...
Geez, this is so annoying I just had IBM and now they have International Business Machines Corp.
Super annoying that that grew but I can't find any way to change that.
All right, now this is annoying. I was trying to get Sony SNL...
it's SNE, so I'm gonna come here I'm gonna type SNE and they will go out and it worked.
All right, so that's all great. Now, yesterday I showed you how we could show a card.
All right, so you come here and you click on this on show card or CRTL+SHFT+F5 and you can get all this data.
All right, so they have previous close, they have the open, high/low, 52 week high, 52 week low, volume, price to earnings ratio, the CEO.
All right, now if we want one of these like previous close.
Let's say just hover and this symbol appears and that data will show up in the grid.
If it wasn't a table, it only shows up in cell C2 but because this is a table, it gets copied down to all of the cells.
So great reason to use the table like that.
You can also come out here, I think I showed this yesterday, and choose one of these items or you can just come here. This is so cool.
And type price and if that's one of those elements, BAM, they will figure it out and add it in.
All right, now, while this is super awesome cool, the people who really trade stocks every day are gonna just hate this because there's no good way to get the last 10 prices or the last 20 prices or the price for the last 30 days.
All we have is yesterday's close and today's current price.
All right, so I already know this is gonna disappoint a lot of people and, you know, hey, it is what it is.
Up here you can learn about the data sources and they don't just support the US stock markets but they support a whole bunch of different stock prices here and it tells you what countries they support.
So check out all these countries, which exchanges they support, so if you're in one of these countries and I'll scroll through here, you will be able to see those various prices.
My title card today with the oranges I was actually gonna steal a scene from trading places, the a great movie with Dan Aykroyd and I was going to do frozen concentrated orange juice but unfortunately those are not working.
I can't put commodities over there, you know.
So it's not again it's not as wide as having a subscription to one of the big services but there are a lot of choices here.
And, you know, if you just have a simple little, you know, you have a portfolio and you know how many shares you have and you want to get the current stock price to see whether you're up or down today, that's gonna work great for that.
Hey, one thing here, this will not automatically get recalculated with the sheet.
If you want to refresh this you have to come out here, right click, data type and refresh or I suppose that refresh either way should force it to go out and get new prices.
All right, so this isn't designed to be constantly streaming new prices into the worksheet. You have to specifically ask the refresh.
You should also be able to come out here and Data > Refresh All.
All right, so you know just be cautious.
All right, now none of this is gonna work if someone has Excel 2016 or 2013 or even Excel 2019.
You have to be on office 365 to convert this you're going to copy CRT+C and then Home > Paste > Paste as Values, which will correctly convert all of these formulas to values.
These though, are still rich data types so to convert those make sure that you're on a cell that has the icon and then right-click and datatype and convert to text.
If you would have just converted the whole thing to text, well, then column B converts and all those formulas over on the right, turn into errors, right?
So, just really kind of annoying, the way that you have to do it in the correct sequence like I just did.
Watch the outtake if you want to see the the bad outcome of doing it the other way.
All right, so here we have the new Data types Stocks and Geography and these are just the first two.
I'm sure that more will be coming, you know, if you have a great idea for a new data type that you'd like to see in Excel, mention it down in the YouTube comments and I'll send it on to the Excel team.
They are actively looking for ideas of where to go next.
Great new functionality in Excel and again once this is built out, it should be relatively easy for them to add more.
All right, today we talked about Stock data types new in Excel for Office 356.
To download the workbook from today's video, visit the URL in the YouTube description.
Hey, I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
