With Bitcoin managing to scale unprecedented heights over the last few years, the world is fast becoming aware of the investment potential of cryptocurrencies. But as attractive opportunities continue to present themselves, the sheer number of new cryptocurrency projects continues to rise meteorically. Indeed, CoinMarketcap lists almost 1500 tradeable currencies in existence today; and with a huge chunk of them offering spectacular returns, traders, investors and long-term ‘HODL-ers’ are increasingly seeking to diversify their holdings across many different projects.
But how does one go about effectively building and managing a cryptocurrency portfolio? Well, hypothetically speaking, one could just leave their purchases on their respective exchanges and simply rely on those exchanges’ calculations of overall portfolio value. But this is definitely not a recommended solution for several reasons.
Firstly, the valuations provided by exchanges tend not to be particularly accurate. Secondly – and perhaps most crucially, your holdings are not secure; most exchanges are not decentralised, which means they remain vulnerable to hackers, as we have seen on many occasions over the last few years. And finally, there tends to be massive disparity in pricing for each asset across various exchanges. That means the price of an asset held on one particular exchange could be substantially different from its true value were you to cash out at that particular moment. (Of course, it also means that arbitrage opportunities remain prevalent and potentially substantial).
As such, it can be tough to keep track of all your investments as they will invariably be spread across numerous exchanges. Thankfully, there are now a growing number of web-based, mobile-based and spreadsheet-based tools to effectively track and manage your portfolio. We outline some of the most popular here:
Given that CoinMarketCap has become the de facto reference page (but not the only one) for cryptocurrency traders needing to check the latest prices, it is now possible for the data published on this page to be imported into spreadsheets, such as Microsoft Excel and Google Sheets, for further analysis. This can be done using CoinMarketCap’s API (Application Programming Interface).
The API used to import data into Excel is steadily growing in terms of capability. As of today, a wide range of functions can be implemented that allow:
- the entire CoinMarketCap database to be imported
- → https://api.coinmarketcap.com/v1/ticker/
- just a select range of coins to be imported (e.g. the top 10)
- → https://api.coinmarketcap.com/v1/ticker/?limit=10
- the price, 24-hour volume and market cap to be converted to another currency → https://api.coinmarketcap.com/v1/ticker/?convert=EUR
- just the data for one specific currency (e.g. Bitcoin) to be imported
- → https://api.coinmarketcap.com/v1/ticker/bitcoin/
Depending on your version of Excel, you can either use the ‘POWER QUERY → From Web’ tab or the ‘Get Data → From Web’ option to import the appropriate data. The latter option is illustrated below:
The CoinMarketCap API address is entered, as shown above. This will enable Excel to pull in all of the data for all of the coins listed on the website, and will produce the following list:
This list then needs to be converted to a readable to table, so select the ‘To Table’ button on the left and then press ‘OK’.
As below, select the button with the two arrows next to ‘Column 1’, which will bring up the entries that will be included in the final table (also, make sure to UNCHECK the box for ‘Use original column as prefix’). And press OK:
We now have the details for each coin as provided on CoinMarketCap, including the symbol, price, volume etc. We also now have our query, which can be renamed to something suitable, such as ‘Live Crypto Data’’, as follows:
Once this is done, select the ‘Close & Load’ button (at the top-left in the picture above), which will load the final table into Excel:
As shown above, in order to refresh the table so that it is pulling in the latest, most up-to-date numbers, select ‘Data → Refresh All’.
As CoinMarketCap’s API site currently shows, a range of specific data can be pulled in from the site and can also be converted into different currencies (for example, https://api.coinmarketcap.com/v1/ticker/?convert=EUR).
Pulling in Historical Data
Of course, as a shrewd investor, you will also want to have the ability to analyse a coin’s previous price action and trends, in order to gain insight into how it might behave in the future. That’s where its historical price data will prove useful.
But the CoinMarketCap API for ‘Historical Data’ remains in ‘Coming Soon’ mode, which means that it is not possible to extract historical data that will continuously update with every day that passes. It is, however, still possible to pull historical prices into Excel and then perform a regular, minor manual update in order to refresh the prices to include the most recent data (i.e. for yesterday’s close).
Customising the date, moreover, gives us a web address that includes the data range; so if we choose our date range as ‘All Time’, the corresponding web address is https://coinmarketcap.com/currencies/ethereum/historical-data/?start=20130428&end=20180123.
As such, the web address gives us a start data and end date for the data. And so, we can use this address to pull the historical data into Excel. Indeed, this is done using a similar method to how we extracted the live prices, as above. Again, we use the ‘POWER QUERY → From Web’ tab or the ‘Get Data → From Web’ option, but this time, we add our new website into the ‘From Web’ dialog box.
Once connected, the Navigator dialog box will show up. If you select ‘Table 0’ from the left column, and the tab ‘Table View’ at the top, you will be able to see the table of historical Ether prices:
The snag now is that to refresh the historical data to the latest available date, you will need to perform a manual update. First, launch the Query Editor on the spreadsheet as follows:
At the top of the Query Editor, select ‘Advanced Editor’, after which you can go in and manually update the last date, which will then feed back into the data.
Google Sheets is proving to be just as useful as MS Excel when it comes to spreadsheet-based tools for portfolio management. And in particular, the CRYPTOFINANCE () function which is available as a Google Sheets Add-On, enables your spreadsheet to access the CoinMarketCap API to return a wealth of important data for each cryptocurrency, including live exchange rates, percentage price changes, market capitalisations, trading volume and circulating token supply.
=CRYPTOFINANCE(“COINMARKETCAP”) returns all of the CoinMarketCap data into one single sheet
=CRYPTOFINANCE(“COINMARKETCAP”, “EUR”) shows the prices in Euos
=CRYPTOFINANCE(“COINMARKETCAP”, “ETH”) shows the prices in terms of Ether
=CRYPTOFINANCE(“COINMARKETCAP”, 100) shows the top 100 coins only
=CRYPTOFINANCE(“NEO/GBP”) shows only the price of NEO in Pound Sterling.
As the Add-On Sheet specifies, a whole host of additional functions can be executed, as per the guide sheet. And what’s more, the add-on provides some capability to access the latest historical data.
If you don’t want to deal with the complications or memory-sapping issues normally associated with spreadsheet-based programmes, then a steadily growing number of convenient mobile applications focused on portfolio management might better suit your needs.
The most popular mobile app currently on the market for monitoring your crypto portfolio is Blockfolio. It has an easy-to-use interface that makes tracking your portfolio a comparatively straightforward process:
This layout makes viewing your portfolio on your phone very easy. But make no mistake, this is not a limited app by any means; indeed, there are several useful tools that have made Blockfolio a hit among both beginners and sophisticated traders alike.
For instance, you can set ‘alerts’ to let you know when one or more of your coins have reached a certain price level:
This is extremely useful, especially given that cryptocurrency markets can be very liquid 24 hours a day, and 7 days a week, so you don’t want to miss that important rally when you’re asleep. As you can also see above, you can select the actual exchange on which your specific coin is trading, which means you are getting the most accurate prices in case you want to buy or sell.
Also, by tapping the newspaper-type icon at the top of the screen, you will gain access to an RSS feed of the latest cryptocurrency news from a wide range of popular cryptocurrency publications, including CoinDesk, CoinTelegraph, BitCoinist and Reddit.
And if all that doesn’t convince you, just check the app’s rating on Google Play, which currently stands at 4.7/5, from just under 60,000 (mostly) satisfied users.
For the more serious investor, however, the CoinTracking app is perhaps the most widely respected. It handily combines both a mobile and web-based platform to pack in a number of highly sought-after features.
For a start, you can import the previous transactions you have made on a particular exchange, either by uploading .CSV file exports from the exchange, or automatically using API (although this requires the paid version of the product to execute).
And given the greater focus authorities are likely to place on ensuring taxes from cryptocurrency earnings are appropriately paid by investors, CoinTracking provides the unique ability for you to prepare a tax report; indeed, with several customisable options available to account for different accounting methods and pricing options, you can construct a detailed, highly tailorised report.
There are a wealth of additional functions on offer to the CoinTracking user, particularly on the ‘Dashboard’ which includes a timeline feature that shows when you first bought a specific coin, the number of times you have traded each month, and the specific days on which your portfolio hit its lowest and highest values respectively. A “balance report” is also available that displays your portfolio with trends for the last hour, 24 hours, week, and month. Trade statistics and some basic trade analysis can also be accessed:
If you are looking for a range of metrics related to just one specific currency, then the “XXX-price.org” websites (where XXX = the currency name) are well-formatted for at-a-glance information, especially for those using mobile devices. The information provided includes the current market price, historical prices and charts, token supply statistics, various live prices that the currency is being trading at on different, and the latest cryptocurrency news pertaining to that currency. Such websites currently exist for nearly all of the highest valued projects including Bitcoin, Ethereum, Bitcoin Cash, Cardano, Litecoin, IOTA, NEM, Dash, Monero and EOS.
If you’re looking for a much simpler, uncomplicated portfolio tracker then LiveMarketCap is an ideal online management tool. Using the format from Coinmarketcap.com, LiveMarketCap allows you to very easily add and subtract coins, as well as adjust the volume for each coin you hold. It also shows the daily change of each coin and the overall value, and provides useful information for each coin such as its compound monthly growth rate and inflation.
And as the site mentions, it doesn’t track any of your personal crypto portfolio data, but does use your browser’s local storage capabilities to save all the information regarding your crypto holdings for the next time you access the site.
It is worth emphasising just how important it is to utilise effective portfolio management tools. Although at present, the only asset-type available to the retail investor is the actual underlying cryptocurrency itself, it is unlikely that we will have to wait too long for derivative contracts to be approved. Indeed, Bitcoin Futures contracts are already accessible to institutional participants. And once forward contracts, options and more exotic crypto-based products are accessible to the public, portfolio management will become crucial, especially once the more sophisticated investors among you decide to valuation pricing models that involve the use of derivatives to hedge risk exposure (for example, using the well-known ‘Greeks’ – delta, gamma, theta and vega).
Although we are at the beginning of this long and exciting cryptocurrency journey, it seems only logical that as investors we get into the practice of using the tools to manage our portfolios effectively. This will equip us well for what comes later down that road.