Quick way to get Live Stock Data to ExcelMay 09, 2020
Everyone, this is Jake. I got a couple of requests on how to automate
dataso I thought I should make a
quickvideo on how to do it. This is the unrefined version of how to do this. five comments all very fast back to back from people asking how to get
dataand how to make it update automatically so this is like i have no time and need something done right now this is my solution I shoot these videos at night during my time, which means the US stock market is closed. I just don't have time to record these videos during the day when the US stock market is open, so I'm pulling out Hang Seng, which is the Chinese index, just so we can have some real live data so we can see this is actually working so what i did was open real
quickthat's another thing i looked up google finance so i'm just do Finance google.com slash Finance because that's the only way and to get there and then HSI is the Hang Seng so HSI pulled this I went and copied this URL you can do this for any stock you want so if you're trying to do the US stock market you can do this it's just now that the markets closed so I'm using a market that's open so we can see it automatically update so I came here and started with just a blank slide or a blank white page.
I'll actually start completely from scratch so we can make a new one there so what you can do is you don't actually even need to record a macro for this you can if you want but you really don't have to you can pull data from the Web. I'm going to come back real quick so I made it quick on the data tab you go from the web and it opens up some kind of browser that doesn't look like much like chrome or firefox or any kind of browser it's just the one that's built into Excel go ahead and paste your url in there if you get warnings and stuff you can hit cancel or continue it doesn't really matter and then you'll see these little arrows we've already done this in previous videos but just for any new viewers these little arrows are how to import data so click on that one to make it a green tick hit import and it will import like it normally does but instead of hitting ok go to properties enable update so update the default is every hour, but we're going to do every minute, save the query, and then also overwrite the existing data; otherwise every time you get new data it just keeps filling up your sheet and that's not helpful so we overwrite the existing you can name it if you really don't want to deal with it hit ok and then I'm going to put a 1 and hit ok and Now, since I imported the entire page, the entire Google Finance page, it actually takes you right up to here in cell b-41.
I don't need all of that, but since I have it updating all the time, it will still get the same data, so I just go to sheet 2. I go the same. XI 1 exclamation point B I think it was 41 if I remember correctly hat there you go and that's the hang seng and now there you just saw that it updates every time you edit a cell it will update so if you just wait for a second and you try to kill time right here, you'll see this. the number will change automatically wherever you go so see it updated I actually have to cut right there because it only updates every 1 minute and having a youtube video with a minute of silence while you wait for the rental number to change isn't worth it nothing so I'm going to put a cut in there to make it look like it happened real quick but it actually takes about a minute for it to update so just wait a minute it will update the price right there and you can have it. automatically enter you can have a bunch of tickers that update frequently and then you just have a list and it will give you a live stock price right there if you want to write a macro to get like give you a follow up history or something you can do that too it's a bit more complicated maybe that's something if there's any demand for it i can show a video on how to do it in the future but i'm trying to keep this video relatively short hopefully less than five minutes so i can come back to your day but you can at least know how to get live data so if you have any questions feel free to comment and thanks for looking
If you have any copyright issue, please Contact