Some tricks with the new Stocks data type in Microsoft Excel. How to refresh the current stock price in Excel. How to change the Stock Market Exchange for one security.
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2265. Refreshing stock data types in Excel.
Hey welcome back to MrExcel netcast, I’m Bill Jelen.
In the last episode, we talked about the new geography data types and stocks have their own complexities. I mean, geography. Geography was easy. Stocks, a little bit harder. So I took types some companies here and took care of the stocks. And the first thing I noticed is I’m getting Barclay’s from the New York stock exchange, not the one that I want from the London stock exchange. So we’re going to right click here, go to data type… Go to data type and say change and that brings us to a search box over here where it will show us all the different stocks that could possibly be Barclay’s. So here, I want the London stock exchange. I choose that and you can see instantly the exchange updates and now we’re in Great Britain Pounds instead of US Dollars. Alright. So that’s the first thing.
Same thing I added yesterday at the last podcast, 15 minutes ago, I set it up as a table so now I can use this item here to get employees or CEO or you know, all kinds of cool stuff like that. Now yesterday we were doing geography and hey the population of the city, I guess it changes every day because people move in, people die, people are born but Wikipedia only updates that, you know, once a year.
But this data here is actually being updated, in many cases every minute.
And be sure to check out this line up here about the data sources, you have to make it wider because they start out not wide enough and it tells you how often things are being updated. 30 minute delays and in some places, the end of the day. So depending on what exchange you’re going for. Just be aware that it’s not being updated every 15 minutes.
So now the question is, how do I force this to update? We can press F9 but that’s not going to do a darn thing because it—these are a different thing. Getting data from the internet, to get this data to actually update, we’re going to come here to the data tab and choose refresh all and then it will be updated like that. And once this Netflix here, it doesn’t know the number of employees or the CEO, so that field there, brand new error, that just means that their data sources don’t have that information currently. It may show up later or whatever. At least stock data types. Now hey, I love this but it’s not perfect, alright. It gives us previous day closed, it gives us 52-week high and low but it won’t give us the last 30 days.
I know a lot of people need the last 30 days, you can plot the trendline, alright. So this is a future version 1 product. It’s cool, it’s not perfect. Hopefully, it will get better.
Now hey, I want to thank you for stopping by. We’ll see you for another netcast from MrExcel
Hey welcome back to MrExcel netcast, I’m Bill Jelen.
In the last episode, we talked about the new geography data types and stocks have their own complexities. I mean, geography. Geography was easy. Stocks, a little bit harder. So I took types some companies here and took care of the stocks. And the first thing I noticed is I’m getting Barclay’s from the New York stock exchange, not the one that I want from the London stock exchange. So we’re going to right click here, go to data type… Go to data type and say change and that brings us to a search box over here where it will show us all the different stocks that could possibly be Barclay’s. So here, I want the London stock exchange. I choose that and you can see instantly the exchange updates and now we’re in Great Britain Pounds instead of US Dollars. Alright. So that’s the first thing.
Same thing I added yesterday at the last podcast, 15 minutes ago, I set it up as a table so now I can use this item here to get employees or CEO or you know, all kinds of cool stuff like that. Now yesterday we were doing geography and hey the population of the city, I guess it changes every day because people move in, people die, people are born but Wikipedia only updates that, you know, once a year.
But this data here is actually being updated, in many cases every minute.
And be sure to check out this line up here about the data sources, you have to make it wider because they start out not wide enough and it tells you how often things are being updated. 30 minute delays and in some places, the end of the day. So depending on what exchange you’re going for. Just be aware that it’s not being updated every 15 minutes.
So now the question is, how do I force this to update? We can press F9 but that’s not going to do a darn thing because it—these are a different thing. Getting data from the internet, to get this data to actually update, we’re going to come here to the data tab and choose refresh all and then it will be updated like that. And once this Netflix here, it doesn’t know the number of employees or the CEO, so that field there, brand new error, that just means that their data sources don’t have that information currently. It may show up later or whatever. At least stock data types. Now hey, I love this but it’s not perfect, alright. It gives us previous day closed, it gives us 52-week high and low but it won’t give us the last 30 days.
I know a lot of people need the last 30 days, you can plot the trendline, alright. So this is a future version 1 product. It’s cool, it’s not perfect. Hopefully, it will get better.
Now hey, I want to thank you for stopping by. We’ll see you for another netcast from MrExcel
