Building a basic portfolio tracker in Excel

Well then, now that we have a nice connection to either the CoinMarketCap API or the CoinGecko API, it is time to start building a simple portfolio tracker in Excel. To not make the post too long, I will try to keep this guide quite basic and mainly suitable for the hold-strategy.

In the example, we will be looking up the coin data using the CMC API, but CoinGecko should work quite similar. The example will also require manual entry of transactions (since we haven't linked to an exchange API to retrieve that sort of data).

Setting up a table for the transactions

For the individual transactions, we are going to use a table in Excel. Tables have the benefit, that they can be referenced by name, and they make expanding with new transactions a whole lot easier. The API data from the links in the first paragraph are also formatted as a table, and can be references as such.

Let's first enter some headers for the table. You can make this as expansive as you like, with all types of data. In the example, I will use the date, coin ID, amount, price paid (in USD) and current price. Once that is done, we select the 5 cells containing our header data and click 'Format as Table' in the Home tab of the ribbon.

Choose your own fancy table style from the drop-down

Make sure you indicate that your table has headers:

Your table has headers, so let Excel know that

Great! Now we have our table ready! It's probably called something nondescript like Table1, and if you really want to be all "clean code compliant" you rename it in the 'Table Design' tab in the ribbon (with the table selected) to something more descriptive.

Let's enter our first transaction in the newly created table. On September 1, 2021 I have bought 1.00 Bitcoin (Coin ID: BTC) for the price of $20000 (got lucky with this one!)

For the current price, we're going to need to reference our CoinMarketCap API results. As per the previous post, we have this data on a separate Worksheet in a table (probably) called 'latest_start_1_limit_5000_convert_USD'. We'll use a simple VLOOKUP formula to get the price this time:

=VLOOKUP([@[Coin ID]],latest_start_1_limit_5000_convert_USD[[symbol]:[quote.USD.price]],12,0)

Since the Coin ID is in the third column of the CMC API data called 'symbol', we select the range in that table from latest_start_1_limit_5000_convert_USD[[symbol] to [quote.USD.price]] where the 12th column holds the price. This all only holds true, if the guide from the previous post was followed. If not, you might need to set a different range yourself.

Et voila! We have the current price of the coin in the latest column, which we can update by going to the Data tab in the ribbon and clicking on 'Refresh all'

Refresh to get the latest prices

Not only do we want to know the price for 1 coin/token, we also want to know the price for the amount we bought on said date, so we can compare. We can add a new column in the table, simply by typing the header name (Value) in the next free column (in this case column F). The table will automatically expand to accommodate the new entry:

New column added to the table

To calculate the value of the amount bought, the following formula should do the trick: =[@[Current price]]*[@Amount]

Now, it is up to you to expand upon this simple tracking tool you've just built. Set target TP prices for each transaction, create an overview sheet consolidating all your holdings, you might even write a macro to auto-update the prices at certain time intervals with fancy flashing colors. The sky is the limit and the world is yours!

Disclaimer: connecting to an API is easier to describe than something like this, due to the specific setups of each individual. Maybe you've moved a single column, maybe you've added something I haven't foreseen... Going into more detail and complexity is therefore out of my current scope of abilities. Heck, this might even not work for some/most users. Feel free to ask away in the comments though :)