How to Get Real-Time Cryptocurrency Prices from Yahoo! Finance to Excel

This video accompanies my post at

Hello world its george from george jameth calm and in this video i’m going to show you how to get real-time cryptocurrency prices from yahoo finance into excel so this is a company is a post that i wrote on the blog i will link to that in the notes for this video now what we’re gonna do is dip into yahoo finance which has been a stalwart source for financial data

For analysts in excel we’re gonna do this with crypto currencies so here’s this link right here i’m gonna copy this and i’ll open this up so you can kind of see what this link does so here’s a table and yahoo finance with all of our crypto currency prices volumes and so forth the goal is to take this information and we’re gonna make this a table in excel that will

Update any time this thing updates and we can get the real-time crypto prices that way so i’m in a new workbook here and in the data tab there’s a getting transform tab here we’re going to do a new query from other sources and from the web and this takes a while so i might speed through some of this so we want a query from the web the url is this financed a yahoo

Complex cryptocurrencies i’ll put that in the notes as well we’re gonna hit ok and then this part takes a while as well okay eventually you should see something like this that takes a while to set up the query but it’s definitely worth it so this ran a query of this website this url and we’re going to go to this table 0 here and this should look pretty familiar

It’s basically what we got from the website so i’m just going to load this okay so we have a table here still loading again there’s a lot of low time when you do this okay and we’re loaded first thing i’m gonna do is change the name of this thing table 0 it’s not all that helpful so i’m just gonna call this like crypto prices or something like that you can even

See also  Contractor Financing: Project Cash Flow Estimating Calculator

Put a description on here so i’ll say like yahoo finance crypto prices okay okay so a couple of things to keep in mind when you’re working with table in excel so this is essentially a table that is linked to yahoo finance so anytime you want to get an update on these prices i’m just gonna refresh the table so you can right click anywhere inside this table we’re

Going to do refresh and if you see the spinning globe down here running background query that means that it’s working i’m gonna back out of this because we just got the table so these prices probably didn’t update that much anyway so click here to cancel sure i’ll do that stop refresh okay close so we’re done with that so here we have a table the only thing that

Kind of bugs me with this table is these prices and everything in here you can just go ahead and type right over now if you refresh you’re gonna get the new prices so i’ll demonstrate here like i’m just gonna type over this and this and make a big mess out of the spreadsheet now the nice thing is if i refresh this and i’ll let it run i might cut through some of

The runtime but when we get back to this table that’s been refreshed we’re gonna get the actual values and these cells and not the ones that i’m a typo so ver so that’s i don’t know kind of annoying to me that you can easily mess up this spreadsheet here so i’m gonna actually do a worksheet protection if you go up to review i’m gonna protect the sheet here and

This will allow different things that the user can do so i still want the user to be able to format cells and columns because these are a little messy right now we might want to label these as like dollars or something like that so i’m not going to password protect it i mean this is public information anyway so there’s really no reason to password protect it but

See also  Finance Manager Interview Questions and Answers

I do want to protect the sheet so that there’s no user errors and making a typo over any of this information i’m gonna hit ok so now the nice thing is like if i wanted to kyo ver this i’m not going to be able to but the downside is that if i want to refresh it i can’t do that either and the only way that as far as i know to overcome this is with vba now if you’re

Really interested in learning vba and that’s a great skill to have i highly recommend john a camp or –is vba pro course john is a great friend of the blog over at excel campus and he does a really awesome course in vba so i’m gonna link to that in the notes as well but for now we’re just going to give this macro running in vba and if you really want to get a grip

On how it works and how to build your own definitely head over to john’s course ok now i’m back on my blog post i have a vp8 code here from our friends that extend office that’s going to allow us to refresh the workbook even though the worksheet is protected so i’m just going to copy this i’m gonna go back to our workbook here now this is an excel macro and to

Get this excel macro running in our workbook we’re gonna have to save this as a macro enabled workbook so i’m going to make it an excel macro enabled workbook i’ll just name it like crypto or something like that all f11 is going to get us into the heart of the vba operations and all you have to do if you don’t know vba right now just follow along with these steps

See also  How I Got My 6-Figure Finance Job Right Out of College

And we’ll get you running fine so microsoft excel objects we’re in this crypto xls and workbook right now which is what we’ve been working in i’m gonna insert a module and now we see this extra folder here module module one okay so we have all this information that i got from the site and i’m just going to go ahead and close this so now when we go back to developer

And macros we see two macros here you’re gonna want to read this data refresh this first one now see that globe is running i’m gonna cancel stop refresh that’s fine downloaded is not complete because we stopped it so now we can and i like to so you don’t have to go through this clunky thing i like using keyboard shortcuts so if i go to macros date of refresh i’m

Gonna go to options i’m gonna do ctrl shift c c for crypto cancel so now when i do ctrl shift and c we see that the query is running and i’m gonna cancel it again so now we have a workbook in this exercise file is up at the blog at george shame on comm again there’s an accompanying post for this so please head over to the site again george de montcalm get your

Free ebook the beginner’s guide to getting hired with excel you’ll get the latest blog posts i’ve got a couple years worth of excel and business analytics related blog posts on the site so please stop by i’ve got my social media right here and let’s stay connected and best wishes with your crypto currencies and with your spreadsheets

Transcribed from video
How to Get Real-Time Cryptocurrency Prices from Yahoo! Finance to Excel By George Mount