Quick way to get Live Stock Data to Excel
May 09, 2020Everyone, this is Jake, I've had a couple of requests about how to automate
live
stock
data
, so I thought I'd do aquick
video on how to do it. This is the raw version of how to do this. I only saw like four or five comments, all veryquick
ly, one after another, from people asking how to getlive
stock
data
and how to make it update automatically, so this is like you don't have any free time and you need to do something now same. this is my solution. I record 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 the Hang Seng, which is the Chinese index, just so we can have some real live data for you. we can see that this is actually working so what I did was I opened up real quick that's another thing I opened up Google Finance so just do Finance google.com bar Finance because that's the only way to get there and then HSI is the Hang Seng, so HSI put this out, I went and copied this URL, you can do this for any stock you want, so if you're trying to do it the US stock market, you can do it, it's right now that the markets closed so I'm using a market that's open so we can see it update automatically so I went over here and started just from a blank slide or a blank page.
In fact, I'm going to start completely from scratch, so we'll make a new one there, so what you can do is you actually don't even need to record a macro for this, you can do that if you want, but you don't really need to. You can do it. extract data from the web. I'm going to come back real quick, so I did it quickly in the data tab, you go from the web and it opens up some kind of browser that doesn't look much like Chrome or Firefox or any type of browser, it's just the one that's built in. in Excel, go ahead and paste your URL in there if you get warnings and things you can hit cancel or continue, it doesn't really matter and then you'll see these little arrows that we've already done in previous videos, but just for new viewers, these little arrows show how import data, so click on that one to make it a green check, hit import and it will import like normal, but instead of hitting OK, go to properties, enable updating, so update the default value. is every hour, but we will save the query every minute and then also overwrite the existing data; otherwise every time you get new data it will keep filling your sheet and that is not useful so we will overwrite the existing ones.
You can name it if you really don't want to deal with it, hit OK and then I'll still put in a 1 and hit OK and now since I imported the entire page, the entire Google Finance page, it actually takes it 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 keep entering the same data, so I go to sheet 2 and I go to equal to XI 1 exclamation point B. I think it was 41 if I remember correctly. there you go and that's the hang seng and now you saw it update every time you edit a cell, it will update, so if you wait a second and try to kill some time right here, you'll see this number. change automatically there you have it and see it updated.
In fact, I have to make a 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 is worthless, so I'm going to make a cut there so that It seems like it happened very quickly, but it actually takes about a minute to update, so just wait a minute, the price will update right there and you can receive it. automatically, you can have a bunch of tickers, all updated frequently and then you just have a list and it will give you a live action price right there if you want to write a macro to get, like give it a tracking history or something you can also do It's a little bit more complicated maybe it's something if there's any demand for that.
I may show a video on how to do it in the future, but I'm trying to keep this video relatively short, hopefully under five minutes so you can go back to your day, but at least you can know how to get live data, so If you have any questions, feel free to comment and thanks for watching.
If you have any copyright issue, please Contact