Get Crypto Prices for Multiple Coins Into Excel Using Power Query

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 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

  1. Connect to the data source using Power Query.
  2. Download the files into a folder.
  3. Combine and Transform the files in Power Query.
  4. Modify the data to make it consistent.
  5. Update the data.

Step 1: Connect to the data source using Power Query

In this example, I’m using 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 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:

Historical price data for bitcoin.

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:

Crypto excel files in a folder.

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:

Selecting to get data from a folder.

Then navigate to the folder which contains your downloads:

Folder which contains downloaded files to consolidate in power query.

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:

Selecting the option to open the files as a CSV document.

Then, at the next screen, you’ll see that the data has correctly been broken out into columns.

CSV data in Power Query broken out into separate columns.

This is how the data looks loaded in Power Query:

Combined crypto data loaded into 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 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.

Extracting text before delimiter in power query.

Then, I’ll rename the first two headers to ‘symbol’ and ‘date’

Power Query table after renaming columns.

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:

Extracting the first characters in a Power Query field.

Specify 10 for the number of characters, and Power Query will remove the rest:

Power Query table after the date field has kept only the first 10 characters.

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:

Pivoting a column in Power Query.

Select ‘price’ as the values column. Then, the end result should show the crypto prices going across:

Power Query table with crypto prices going horizontally and dates going vertically.

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:

Refreshing data in Power Query.

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.


How to Calculate Crypto Coin Dominance Using Power Query in Excel

Crypto coin dominance is a vital metric in the cryptocurrency market, providing insights into the relative market strength of a particular cryptocurrency, compared to the overall market. It reflects the proportion of a specific cryptocurrency’s market capitalization in relation to the total market cap of all cryptocurrencies. This metric is particularly significant for investors as it indicates the level of risk, market sentiment, and the dominance of major players like Bitcoin and Ethereum.

Formula for calculating crypto coin dominance

To calculate crypto coin dominance, you the formula itself is fairly straightforward: divide the market capitalization of the cryptocurrency in question by the total market capitalization of all cryptocurrencies. Market capitalization, in this context, is calculated by multiplying the current price of the crypto coin by its total circulating supply.

For instance, if Bitcoin has a market cap of $700 billion and the total market cap of all cryptocurrencies is $2 trillion, Bitcoin’s dominance would be 35%.

Why is this useful for investors?

Crypto coin dominance helps investors understand the weight of a particular cryptocurrency in the market, aiding in diversification and risk assessment strategies. A high dominance might suggest a more stable investment but with potentially lower growth prospects, while a lower dominance could indicate a more volatile but possibly high-growth opportunity. Additionally, shifts in dominance can signal broader market trends, helping investors to anticipate and react to market movements.

Calculating crypto coin dominance in Excel is a practical way for investors to actively monitor these shifts and make informed decisions. By regularly updating the market cap data for various cryptocurrencies, investors can use Excel to quickly calculate and track changes in dominance, enabling them to identify trends and adjust their portfolios accordingly.

Pulling in crypto market caps into Excel

To calculate coin dominance in Excel, you can use Power Query. Through Power Query, you can pull in the data, and do calculations to determine the dominance percentage.

You can find a list of the top cryptos by market cap from the following URL in Yahoo! Finance:

To get this data into Excel, take the following steps:

Go to the Data tab and select ‘From Web’

Selecting the From Web option in the Data section of Excel.

Paste the link the following prompt and click OK.

Using the From Web option in Power Query to import data.

Select the table in Power Query which contains data on the crypto market caps. Then Load the data.

Selecting a table to import from Power Query.

The data is now in Excel but there are no calculations happening just yet. To get this to work, we need the total crypto market cap. The table in Yahoo! Finance didn’t have this information readily available to pull into Power Query. Instead, I’ll leave a place in Excel where the data can easily be entered.

Currently, the crypto market cap is $1.8 trillion. Since the majority of crypto market caps are in billions, I’ll put this in the form of billions, as 1,810.

Exporting data into Power Query from a range in Excel.

Next, I’ll load this into Power Query. With one of the cells selected, click on the Data tab again, this time, select From Table/Range

Exporting data into Power Query using the From Table/Range option.

In Power Query, I’m going to rename this table MarketCap and the other one as YahooFinance.

Calculating coin dominance in Power Query

Before I start my calculations, I need to first convert the market caps from the YahooFinance in terms of billions, and remove the ‘B’ that comes after them. Currently, those values are reading as text, and they need to be numbers. In the YahooFinance table, highlight the column which contains the market cap. Then, on the Transform tab, click the Extract option and select Text Before Delimiter

Selecting a column in Power Query to parse out numbers from.

Just enter the letter ‘B’ for the delimiter and press OK on the next screen.

Parsing out the text before a delimiter in Power Query.

Then, it’s necessary to convert the text into numbers. For that, select the ABC indicator on the header for the market cap, and select Decimal Number for the type.

Converting a column into a decimal number format.

After doing so, the data in the column aligns to the right, indicating the field is a number.

The last part involves creating a new column to calculate the market dominance percentage. For this, go onto the Add Column tab in Power Query. Then, select the option for a Custom Column.

Adding a custom column in Power Query.

I’m going to name the column ‘Dominance’ and this is where I’ll need to enter my formula. The numerator will be market cap column, which in this case is Column9. I will divide this by the value in the MarketCap table. To reference that value, I need to use the following syntax: MarketCap[MarketCap](0}.

The table is called MarketCap, as is the field name, which is in parenthesis. Since I want the first value in the list, I use {0} since it is a 0-based index. That returns the total market cap I entered of 1,810. To put this all together, this is what the formula looks like in my Custom Column calculation:

Custom column calculation in Power Query which calculations coin dominance percentage.

After clicking OK, now my dominance column appears. Now I can load the data into Excel. All that’s left is to convert the column into a percentage.

Excel table showing crypto coin dominance.

Power Query will create another sheet for the MarketCap, but you can delete that as it isn’t necessary.

Updating the file

Moving forward, to update the calculations, all you need to do is update the MarketCap value. This is the only value that won’t pull in automatically from the Yahoo! Finance link. Then, click on the Refresh button on the Data tab and Power Query will pull in the updated market caps for the top cryptocurrencies, and then do the dominance percentage calculation.

Refreshing the data in Power Query.

Ideally, the total market cap could also be pulled in. However, given the vast number of cryptocurrencies, the list in Yahoo! Finance isn’t comprehensive enough; nor is there a table to pull just from market cap. If you do come across a better source for pulling in crypto market caps, let me know!

If you liked this post on How to Calculate Crypto Coin Dominance Using Power Query in Excel, 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.