Creating your own crypto portfolio tracker is a cheap and easy way to help you keep on top of your cryptocurrency investments.
As you may know, once you start holding altcoins in different exchanges it can be exponentially more difficult to keep track of your overall portfolio performance.
That is exactly why in this article I’m going to teach you how to make your own cryptocurrency portfolio tracker using excel.
All methods used in this tutorial are completely free.
Free Bonus: Click Here To Get A Free Introduction To Financial Analytics in Spreadsheets and learn the basics of spreadsheets by working with rows, columns, addresses, and ranges.
Affiliate Disclaimer: We sometimes use affiliate links in our content. This won’t cost you anything, but it helps keep our lights on and pays our writing and developer teams. We appreciate your support!
How To Build A Cryptocurrency Portfolio Tracker In Excel
Step 1: Download cryptosheets addon
Cryptosheets is by far one of the best ways that we have found to import altcoins and the main cryptocurrency coins in excel.
This is because it automatically tracks your crypto and updates their prices accordingly.
The most important aspect is it allows you to also track altcoins such as doge coins, crypto coins, you name it.
Whereas methods such as web scraping or using Excel’s “stocks button” may not be as intuitive, as they do not provide all the altcoins. also, it can be quite cumbersome.
If you want to learn more about other alternative methods that you can import cryptocurrency prices in Excel, you can read our other article “How To Import Cryptocurrency Prices Using Excel“.
To download our add-in you can follow these steps:
- Click on Insert header on Excel
- Click on Add-ins
- Click on Get Add-ins
- Search for Cryptosheets
- Click on Add
- Tick “I agree to all the above terms & conditions”
- Click on Continue
Step 2: Create an account with cryptosheets
Once you have successfully downloaded your cryptosheets addon, it should appear as a new header on your excel.
However, you’re not finished yet.
One of the requirements is to make an account with cryptosheets which is completely free.
Without making an account you won’t be able to use any of the functions as it will return a suspense error.
To make an account, you can follow these steps:
- Click on Cryptosheets
- Click on Register
- Click on Register in the pop up box
- Follow the prompts and create your account
Step 3: Import our live cryptocurrency prices
Now that we have created and signed into our accounts, we can use all the available functions.
To import live crypto prices in excel we can use the function CS.EXRATE(CRYPTO-TICKER SYMBOL”, “USD”, “Refresh time”).
Basically, you just need to declare the crypto ticker symbol then followed by the relevant currency you want to display and the time it refreshes.
For example, if I want to display the price of bitcoin in USD and update every second, it would be cs.price(“BTC”, “USD”, “1”).
You can learn more from the article “What’s the difference between CS.EXRATE vs. CS.PRICE vs. CS.PRICEA?“.
Step 3: Create our portfolio investment table
A portfolio investment table shows the statistics of our portfolio, such as our purchase price, the current value, how much we invested, total return and etc.
As shown in the image above, in this step we will basically be filling in each header section of our table.
Below I have listed each column header and explained in-depth exactly how I made the formulas.
Purchase price: The purchase price is basically the average purchase price of your crypto coin, for instance, if you bought 5 bitcoins on average of $30,000. You would enter $30,000.
Numer of currency held: The number of currency held, is how much currency of each coin you currently hold. For example, if you have 5 bitcoins purchased an average of $30,000, you would enter 5.
Amount invested: The amount invested is basically the total amounts you invested for each coin, since we already have the number of coins purchased and our average purchased price, you can just use the function =Purchase price of coin * Number of currency held.
Market value: The market value shows the worth of our current crypto coin, the formula we can use is =Last Price * Number of currency held.
Dollar change: Dollar change is basically how much profit or loss we have occurred since investing in cryptocurrencies, to calculate the dollar change you can use the formula =Market Value – Amount invested.
% Gain: The percentage gain shows us how much our crypto portfolio has changed since we started investing, to do this. It is just a simple formula of =Dollar Change / Amount Invested
Total Initial Investment: The total initial investment is the total amount you have invested in cash to your cryptocurrency coins, this is just the sum total of your entire Amount invested column which the formula is =sum(Entire Amount Invested column)
Total Market value: The total market value shows our current portfolio worth, this is just the sum of our entire market value column which is =sum(Entire Market Value Column).
Portfolio gain: To calculate our total portfolio gain we can use the formula =Total Market Value – Total Initial Investment.
Total % Change: For the total percentage change of our entire portfolio it is just the amount of money we made divide by our initial investment which is =Portfolio gain/Total Initial Investment.
Step 4: Insert a distribution pie chart
A distribution pie chart is a graphical representation of the diversity in our portfolio, it helps with providing a quick visual of our cryptocurrency holdings and the relative weight of its worth compared to the entire portfolio.
To make a distribution pie chart you can just copy the cryptocurrency ticker column and the weight column then to import the pie chart you follow these steps:
- Highlight the entire cell values of the newly generated table
- Click on Insert
- Click on the Pie chart Icon
- Click on a Pie chart
Step 5: Create a Return on investment Column Graph
A return on investment column graph shows our percentage return on investment. This is a great way to show the cryptocurrencies which are making money and others that are stagnant.
To make an investment bar graph, it’s pretty much the same as our pie chart. Where you copy the cryptocurrency ticker symbol and the relative % gain.
Afterward, to visualize our data you follow these steps:
- Highlight the entire cell values of the newly generated table
- Click on Insert
- Click on the bar chart icon
- Click on a bar chart
Free Excel Cryptocurrency Portfolio Tracker Spreadsheet Download
We have attached a finalized copy of our homemade free-to-use cryptocurrency portfolio tracker spreadsheet which you can download using the button below.
You will need to download the cryptosheets addon. If you have not done so already.
If you like our work and would like to support us by learning, then we suggest trying a FREE trial in one of Skillshares Online courses:
You can also download free cryptocurrency templates on cryptosheets by following these steps:
- Click on cryptosheets
- Click on Templates
- Click on Load Templates
Benefits to making your own cryptocurrency protoflio tracker
- Track all your crypto investments: Having an excel spreadsheet to track your crypto portfolio centralizes all your investments at one place. This is extremely useful if you hold altcoins in multiple exchanges, ICOS and even crypto in cold wallets.
- Make your own tailored analysis: Excel is widely known for its ability to conduct financial analysis. This means you can make your own personalized graphs, tables and many more personalized features to track your portfolio.
- Identify underperforming cryptocurrencies: Since you have all your crypto tracking at one place, you can quickly identify the investments that are underperforming.
- Automatically updates and tracks your portfolio: The method we that we’re showing you in this tutorial automatically tacks and updates cryptocurrency prices which allows you to make a live crypto portfolio. This is extremely useful if you like to see a centralized overview of all your entire crypto protfolio.
You can create a crypto spreadsheet in excel using cryptosheets addon, this addon automatically gets live prices of cryptocurrencies.
You can import crypto prices into excel using the cryptosheets addon in excel.
You can keep track of your cryptocurrency portfolio by making a live tracker using excel spreadsheets with the cryptosheets addon.