Posts in category: crypto

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:

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

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: https://finance.yahoo.com/u/yahoo-finance/watchlists/crypto-top-market-cap/

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

Go to the Data tab and select ‘From Web’

Paste the link the following prompt and click OK.

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

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.

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

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

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

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.

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.

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:

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.

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.

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.