Do you want an easy way to pull in crypto prices for multiple coins? Using Power Query in Excel, you can accomplish this by connecting to a website such a coingecko.com and downloading the historical values. Below, I’ll show you how you can pull in historical prices for multiple coins at once, and how you can easily update the values in the future.
Here are the steps to download crypto prices and import them into Power Query from coingecko.com:
- Connect to the data source using Power Query.
- Download the files into a folder.
- Combine and Transform the files in Power Query.
- Modify the data to make it consistent.
- Update the data.
Step 1: Connect to the data source using Power Query
In this example, I’m using coingecko.com as my source but the process may be similar for other sources. As long as the data as in a table format, there may not be a big difference in the process, however, there could be subtle changes in how you get data from one site versus another.
First, navigate to the coingecko.com website to the cryptocurrency’s price history that you want to download. On the historical data tab, there is a link to download the data:
Step 2: Download the files into a folder
Download this file and save it into a folder. Repeat the process for any other cryptocurrencies that you want to track historical price information for. In this example, I’ve downloaded the price history for Bitcoin, Ethereum, Shiba Inu, and Dogecoin, and saved it within a folder called ‘Crypto’ on my computer:
Step 3: Combine and transform the files in Power Query
Now that all the files have been downloaded, I can use Power Query to consolidate them. With all the files in a folder, I can go and select to get data From Folder:
Then navigate to the folder which contains your downloads:
Since you are selecting a folder, you won’t see the individual Excel files that you have saved — this is fine. Only if you were selecting files would you see the actual files. Once you’ve selected the correct folder, click on the Open button.
Next, select the option to Combine &Transform the files. If you get an error saying that it is an unexpected format, you may need to click on Edit on the next screen. This is because in this example, the data is in a comma-separated value format. After clicking edit, make sure you select the option for a CSV document:
Then, at the next screen, you’ll see that the data has correctly been broken out into columns.
This is how the data looks loaded in Power Query:
Step 4: Modify the data to make it consistent
There are multiple changes that need to be made to the file. The first is to adjust the source.name field so that it reflects the coin and doesn’t include the full filename. To do this, click on the Transform tab and select the option to Extract Text Before Delimiter and use – as the delimiter.
Then, I’ll rename the first two headers to ‘symbol’ and ‘date’
Next, to correctly parse out the date, I’m going to grab the first 10 characters in the field. To do this, select the date column, and on the Transform tab, select Extract and select First Characters:
Specify 10 for the number of characters, and Power Query will remove the rest:
I’ll remove the market_cap and total_volume columns since they aren’t needed. The last step involves pivoting the data so that the crypto symbols are going across. To do that, select the symbol column, and on the Transform tab, click on the Pivot Column button:
Select ‘price’ as the values column. Then, the end result should show the crypto prices going across:
Step 5: Update the data
Now that the data is all entered in Power Query format, the process of updating it at a later date is fairly straightforward. Simply download the Excel files again, save them into the same folder (overwriting the previous files), and then click on the Refresh Data button on the Data tab:
Clicking the button will refresh all the queries and do all the transformations and adjustments that were made earlier. By setting this all up in Power Query, you can easily repeat the process. Just download the latest data, and then click Refresh All.
If you liked this post on Get Crypto Prices for Multiple Coins Into Excel Using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
