Accounting for cryptocurrency portfolio in Excel

Accounting for cryptocurrency portfolio in Excel

Cryptocurrency portfolio accounting in Excel
At the moment, there are quite a few specialized resources that could entitle the user to fully keep track of the cryptocurrency investment portfolio. And even those applications that already exist do not provide all the necessary functionality. But, oddly enough, using regular Excel spreadsheets can make monitoring your assets much easier.

CRYPTOCURRENCY PORTFOLIO ACCOUNTING IN EXCEL SPREADSHEETS
There are two main tools available in Excel spreadsheets for accounting for a cryptocurrency portfolio:

GOOGLE FINANCE ("BTC/USD") function, which allows you to track the bitcoin rate against the dollar. It is rather primitive and does not provide any other useful information for traders.
The CRYPTOFINANCE add-on includes a number of features and accounting capabilities.
To install the CRYPTOFINANCE addon, you need to go to the "Add-ons" tab, click "Install Add-ons" and in the search box to enter the name of the addon. It is free and available to all.

After that, just click on "+Free" and give access to access to external API. After that, you will be able to use all the provided functionality of this addon.

Let's look at the main functions that are available in it.

MARKET VOLUME
To display the cryptocurrency market volume, you need to enter the following command in a table cell:

=CRYPTOFINANCE("COINMARKETCAP")

It will broadcast data about the trading volume from CoinMarketCap. In this case, if you enter the function as shown above, the table of all cryptocurrencies available on the site will be uploaded to the page. If you want to display data on a particular coin, then enter:

=CRYPTOFINANCE("COINMARKETCAP"; "XXX")

Where XXX is the cryptocurrency token, such as BTC, ETH, LTC, etc. If you enter the marker incorrectly, the system will generate an error.

CRYPTOCURRENCY RATES
To output a cryptocurrency exchange rate against any fiat currency, enter the following command:

=CRYPTOFINANCE("XXX"/"YYYY")

Where XXX is the cryptocurrency token and YYY is the fiat currency token. But that's not all, you can display the value of the coin at a certain point in time:

=CRYPTOFINANCE("XXX/YYYY"; "price"; "YYYY-MM-DD@HH-MM")

You can also use data from different exchanges, for example, for Poloniex this function will look like this

=CRYPTOFINANCE("POLONIEX:BTC/USD")

Here you may also add an instant exchange rate for a certain moment, specifying the date and time.

Instead of a price marker, you can also use:

Close or open - the closing and opening price, respectively.
High or Low - the highest or lowest rate.
CRYPTOCURRENCY CAPITALIZATION
The function is responsible for displaying capitalization information:

=CRYPTOFINANCE("XXX/YYYY"; "market cap")

Where XXX is a cryptocurrency, the capitalization of which will be displayed, YYY is the fiat currency, in which the capitalization level will be shown. If the second parameter is not set, the output will be in US dollars by default.

TRADING VOLUME
This function shows the daily trading volume for a specific cryptocurrency and has the following form:

=CRYPTOFINANCE("XXX/YYYY"; "volume")

Accordingly, as in the examples above, XXX/YYY is the currency pair, denoting which fiat currency will be displayed for a particular coin. The default is USD.

NUMBER OF COINS.
Used to determine the number of coins from different points of view:

=CRYPTOFINANCE("XXX/YYYY"; "marker")

The function marker (marker) can be used:

total_supply - the total number of coins in circulation.
available_supply - the available amount of coins.
max_supply - maximum issue volume.
PRICE FLUCTUATIONS.
Allows you to output the fluctuations of cryptocurrency rates for the past hour (1h), day (24h), and week (7d). In general, the function has the form:

=CRYPTOFINANCE("XXX"; "change"; "TIME")

Where TIME denotes the period of oscillation (1h, 24h, 7d).

OTHER FUNCTIONS.
In addition, there are a number of other functions in the addon that allow you to simplify the accounting of the cryptocurrency portfolio in Excel. We will highlight their two categories.

Coin characteristics: =CRYPTOFINANCE("XXX"; "MARKER"), where you should use:

rank - the coin's place in the ranking.
name - full name of coin by its short designation.
Market characteristics: =CRYPTOFINANCE("GLOBAL"; "MARKER"), where instead of marker you should use:

total_marketcap - capitalization of all cryptocurrencies in dollars.
total_24h_volume - the total volume of trades per day in USD.
active_currencies - total number of coins.
active_markets - the number of active markets.
bitcoin_percentage_of_marketcap - the percentage dominance of bitcoin in the market.
The function also allows monitoring of social networks of coins on the fact of changes in the number of publications. This greatly simplifies the analysis of cryptocurrencies, taking into account the news background. The following functions are presented:

=CRYPTOFINANCE("SOLUME:XXX"; "twitter(reddit)_volume") - the amount of information on a specific cryptocurrency (XXX) in the social networks Reddit or Twitter (specify one of the social networks)
=CRYPTOFINANCE("SOLUME:XXX"; "twitter(reddit)_change") - the change of the number of publications per day on the specific cryptocurrency (XXX) in the Reddit or Twitter social networks (specify one of the social networks).
Keeping track of your cryptocurrency portfolio in Excel is quite an effective solution for those who are seriously engaged in trading. In addition, you won't have to share your portfolio data with third-party resources, which increases the security of your data.