I’ve been a casual student and advocate of cryptocurrencies since ca. 2014, but recent news has attracted me (along with everyone else and their Uber driver) to them more seriously.
Subscribe to my newsletter for your free ebook, “The Beginner’s Guide to Getting Hired with Excel.”
This afternoon, I was keen on finding a way to track crypto prices in real-time on Excel. After a bit of research, I stumbled upon a fascinating solution involving a crypto trading bot from immediate connect that could automate the process seamlessly. Here is what I came up with.
View the YouTube lesson below (don’t forget to subscribe!):
In this lesson, we will use Get & Transform to load real-time crypto data from Yahoo! Finance. Then I will make the resulting table a bit more user-friendly. But if you want to escape the hassle from doing all these, you can head over to platforms like Cryptsy instead.
Download the exercise file here.
Through its Get & Transform modules Excel can import data from a variety of sources. It’s really powerful and this exercise barely scratches its capabilities. For more on Get & Transform, check out Oz du Soleil’s Lynda.com course.
In this lesson we will import crypto prices from Yahoo! Finance. Yahoo! Finance is a stalwart resource for financial analysts and I was eager to find that it would not be too hard to integrate their cryptocurrency price updates into Excel with Get & Transform.
To do this, open a new worksheet, then select Data | New Query | From Other Sources | From Web.
In the URL tab, paste the following link:
https://finance.yahoo.com/cryptocurrencies/
A large box entitled “Navigator” should appear on your screen and you should see something like this:
Click on Table 0 on the left-hand column of your box, then Load toward the lower right.
This process takes time!
It took about a minute on my laptop’s wi-fi with very good signal. So let it run.
The whole process sped up looks like this:
Awesome – you’ve got real-time cryptocurrency prices from Yahoo! Finance delivered straight to your workbook. For updated information, right-click anywhere in that table and select Refresh.
But because I’ve spent too much time in a cubicle with CPAs, I won’t stop there. Oh no, let’s make this a little more user-friendly.
First, notice that if you look over to your right, you’ll see in the Workbook Queries menu that our Table is called Table 0. That’s not very helpful!
So I’m going to head over to that label, right-click and select Properties.
I’ll name the table Cryptos. I could even put a description in the table (“Crypto prices from Yahoo! Finance,” for example).
Nice.
Now after this part you’re going to wonder what happened to me as a child.
Click anywhere in your beautiful table.
Now, go ahead and type right over that.
Did I just cause the collapse of Bitcoin? Well, no, but that’s what the spreadsheet says.
Of course, you can refresh the workbook and all changes will correct.
But I find this really annoying, so I am going to protect my worksheet so users cannot modify our table.
So I click Review | Protect Sheet. I have some options as to what I will allow users to modify. I can also choose whether to protect the sheet with a password. I choose not to, as this is public information anyway.
Nice. Now if you go to key over the table, you’ll get the following error message:
The only problem with this is that now you can no longer refresh your table, because it’s on a protected worksheet!
Damn Excel! Why you making this so difficult?
So let’s pause for a moment, and cope with a meme.
Fortunately, refreshing data in a protected sheet is possible with VBA.
What? No!
Now, I’m not a VBA expert and I am not going to try teaching it. For that, I suggest my friend Jon Acampora’s VBA Pro Course.
Please note that I am an affiliate of Jon’s course and receive a portion of any sales generated with the above links.
In the meantime, I will simply instruct you to save your file as an .xlsm macro-enabled workbook, hit Alt+F11, and paste some code into the Modules section of your worksheet.
Learning VBA is frustrating. Fortunately there is a lot to work with that’s already online.
I was able to borrow the below from our friends at ExtendOffice with little modification:
Now you’ll able to have your workbook protected and refresh it. Simply head to Developer | Macros and you should see your “Data Refresh” and “Data Refresh2” macros. For “Data Refresh” click Options.
See how you can assign a keyboard shortcut for your macro? I’ll do Ctrl + Shift + B. Now when I use that keyboard shortcut, the macro will run, i.e. the table will refresh with the latest data from Yahoo! Finance.
If everything is running smoothly, you’ll see a spinning globe on the lower-left of your screen.
Did this this post help you? Please like, comment and share.
Leave a Reply