[Geko]

You can find in this thread a spreadsheet that I have prepared quickly to monitor cryptos instruments and my portfolio.

The spreadsheet is connected to coinmarketcap API to get the live feeds from the major coins.

Feel free to use it, tweak it and share with us your modifications/improvements

PS: the trades in the spreadsheet are just examples.

Portfolio split per MarketPlace with % allocated per marketplace
(you can note that the left statement do not show your equity if you want to share some infos but want to keep some of your infos private).

Index_1.png

Portfolio split per Instrument (Crypto) with growth %, results, % allocated ...
(you can note that the left statement do not show your equity if you want to share some infos but want to keep some of your infos private).

Index_2.png

You can also display one of the previous month results and floating:

Index_3.png

Statement with results & Floating per month

Index_4.png

In the following post you will find tips about how to use this spreadsheet, and how to connect an XLS spreadsheet to coinmarketcap to get live quotes.

[Geko]

I have connected the spreadsheet to coinmarketcap API thanks to a How to prepared by kul_dood and available on reddit at this link: How to import coinmarketcap data into Excel and have it automatically update .

You can find this how to below for further reference.

Quote:

I know there have been posts on how to insert live prices into Google Sheets but I like using Excel so I set it up in there.

For those who want the quick and dirty and feel like they can put it together with a couple of hints, all you'll need are the following two links:

https://coinmarketcap.com/api/

The youtube video guy downloads a JSON file but I have my data linked directly from the website. Detailed instructions are as follows:

For the purpose of this post, let's say I'm holding 1 BTC, 10 ETH, and a few alts like 100 STEEM, 200 XLM, 300 ARK.

If you visit that API link I posted earlier, you'll see the link you need to connect to for linking live prices. For getting the entire ticker, the API link is https://api.coinmarketcap.com/v1/ticker/

Let's get this ticker imported into excel first. Keep in mind that it's not a good idea to link to values in the ticker because as the coin ranks change, so will the row number that they'll be listed on, which will mess up your links.

To import ticker into excel follow the steps as described below:

1.1. In Excel, under the "Data" tab, click on "New Query", then "From Other Sources", then "From Web."
1.2. Paste the ticker API link (https://api.coinmarketcap.com/v1/ticker/) that they provide in the link above and hit OK.
1.3. This will open up a Query Editor window. I renamed the default name for this query in the right side column from "Query1" to "Ticker." You can change it to whatever you like.
1.4. In the Query Editor, click on "To Table" and OK in the popup that asks a couple of questions. No need to change any of the default settings.
1.5. This is optional but where it says Column1 next to "ABC123" you can double click that and rename it. I renamed it to Coin.
1.6. Click on the small grey box next to "Column1" that I renamed to "Coin." It's a small box with two arrows pointing in the opposite directions. It will open up a popup that allows you to select which property of each coin to display. I left them all selected and just chose to hide the columns later I didn't like.
1.7 Click "Close & Load" in the top left corner of the Query Editor window. Now the Query has been turned into a list that will have each coin as a row and the properties for it in column. However, as it is, this list is not set up to automatically refresh at a given interval.
1.8 Set up automatic refresh by clicking on "Properties" button in the "Design" tab. This will open up the "External Data Properties" popup.
1.9 Click on the grey box next to name entry in the "External Data Properties" popup and select the boxes next to "Refresh every" and "Refresh data when opening the file" boxes. As you can see, you can set up the automatic refresh interval to any number of minutes. The shortest it allows is 1 minute, which is what I have set up. It doesn't allow fractions of a minute....I tried....it gives an error if you put 0.5 or something. Click OK then OK to close both popups.

Now you have a live ticker that will refresh every minute. However, like I mentioned before, you shouldn't use the values in this ticker to link to your own cells because the row that a give coin is listed on will change with their rank. The link will remain on that row, which will show wrong entry. I only imported the ticker to have the entire list, as well as to get the ID of the coin for getting specific API for creating cell links.

To create links for generating your own live and updating tables, we need to similarly get a query but instead of the entire ticker, we need a query for our individual coins that we're holding. In my example, it's BTC, ETH, STEEM, XLM, and ARK.

To import coin data follow the steps as described below:

2.1. Get the API link for your coin of interest from the website I mentioned at the top. It shows as an example, the bitcoin API link is https://api.coinmarketcap.com/v1/ticker/bitcoin/
2.2. Use that link and follow steps 1.1 to 1.5.
2.3. Click on the "Record" which is a link under Column1 and it will turn into a two column table. Once you see in that format, click "Into Table" in the top left corner.
2.4. Proceed similarly from step 1.7 onwards.

You will need to generate a new query for each of your coins. Simply change the bitcoin part in the API link to whichever coin you're interested in. Replace bitcoin with your preferable coin's ID as shown in the ticker. Remember, use the ID, not the coin name. They are not always the same.

For my examples, I would use the following API links:

https://api.coinmarketcap.com/v1/ticker/bitcoin/

https://api.coinmarketcap.com/v1/ticker/ethereum/

https://api.coinmarketcap.com/v1/ticker/steem/

https://api.coinmarketcap.com/v1/ticker/stellar/

https://api.coinmarketcap.com/v1/ticker/ark/

Here is an example of the type of chart I generated with the example.

Hope this was helpful. It seems sometimes when the API data refreshes the excel file switches to that sheet, which is a bit annoying. If anyone knows how to keep the sheet with your data pinned let me know. Also, I can't make sense of the values in the last updated field. If anyone knows how to interpret those let me know as well.

Enjoy!

Edit: Here is a sample portfolio file.

Edit 2: Here are examples of coinbase APIs for those who want more relevant prices. I got the API links from here:

https://api.coinbase.com/v2/prices/ETH-USD/spot

https://api.coinbase.com/v2/prices/BTC-USD/spot

[Geko]

Excel spreadsheet First tab

In this post you can find some explanations about how to use the spreadsheet’s first tab: “10-YTD-Summary”:

Basically, this spreadsheet displays a summary of your trades with various information’s: growth %, results, % allocated per marketplace or instrument… There are not a lot info’s to fill.

At the top of the spreadsheet you have 2 choices:

I1.png

- Calculate summary: if yes, the reporting will be calculated, if no, it won’t. This option is useful to save some CPU if the reporting has to calculate a lot of data.

- Show Open/all: this option allow you to display all trades (all instruments) or only open trades (hide closed trades).

Below on the spreadsheet you have 2 cells where you can change the date to change the reporting period:
(the date which can be changed are highlighted in pale yellow)

I2.png

As a general rule, in the spreadsheet, all cells which needs a manual input are highlighted in pale yellow:

I3.png

The column “this month rst” is showing the current month profit. There is a formula to get automatically the current day date. If you change the date for a previous month date, they report will show the selected month results.

On the right side you have another date which you can change, and which is used to display one of the previous month results. There is a formula to get automatically the last day of the previous month. You can change this date to get another month results.

Finally, on the latest report at the bottom, you have cells which needs to be filled manually:

I4.png

Below the reports there are some formulas which are used to generate those reports. Do not delete them otherwise you won’t get the reports anymore.

[Geko]

Excel spreadsheet Second tab

In this post you can find some explanations about how to use the spreadsheet’s second tab: “11-Trading Desk”:

Basically, this is where you have to fill all your trade’s information’s to generate the reports and track/follow your trades).

All reports are based on this spreadsheet, make sure to fill it with caution.

The column which needs a manual input are highlighted at the top with a pale-yellow colour:

a1.png

There are some examples in the spreadsheet about how to fill it.

The general idea is to fill the entry transactions, then the exit transactions, then the historical values if you want to keep track of them.

Entries

a2.png

- Marketplace: input the marketplace where the transaction is made.
- Instruments: input the crypto name. Make sure to use the proper crypto name to get the live quote updated correctly. If the instrument name is correct, it’s crypto code will appear in the following column. To get the correct crypto name, you can have a look in the “99-CoinMarketCap Live” tab to get the list of cryptos.
- Code: automatic based on the crypto’s name.
- Operation: describe your operations for reference to help to track them. Only 2 operations needs to respect a special case: “Deposit” and “Withdrawal”. Make sure to respect the case to have those kinds of operations properly reported.
- Date: operation date
- Horizon: Horizon time for your trade (LT: Long term, MT: Mid term, ST: Short term)
- Price in: Crucial field, which is used to assess crypto values. Basically, there are 2 fields: USD and BTC. USD must be used for USD transaction and for bitcoin transaction. BTC must be used for cryptos which are quoted in BTC.
- BTC/USD quote: enter the BTC/USD quote for operations which are quoted in BTC.
- Unit price: Enter the instrument’s entry price. For USD transaction, input 1 and input the total amount of your transaction into the volume.
- Volume: input the number of cryptos.

At the end of the entry section you can see the equity in BTC and USD.

You can note than some lines are highlighted in pale-blue. Only lines which still have trades opened are highlighted to make it easier to track open trades.

Exits
For each entry transaction’s, you have up to 4 exit possibilities. Useful if you exit a transaction in 4 times.

The various info’s are really straightforward.

a3.png

At the end of the exit section you can see the exit value in BTC and USD.

Infos

a4.png

History
If you want to keep track of your history, you can also complete the cells on the far-right side of the spreadsheet.

a5.png

If you want to complete those historical information’s afterwards, you can use the formulas at the extreme right-side of the spreadsheet to calculate the crypto value at a specific date. The idea is to input the crypto quote and the BTC quote at the end of a previous month. For more info’s just ask me.

[Geko]

Excel spreadsheet Second tab

In this post you can find some additional explanations about how to use the spreadsheet’s second tab: “11-Trading Desk”:

Here I am going to describe how to fill properly your transactions. I am going to use the examples from the spreadsheet:

- First line: a deposit was made at Lykke exchange on the 01/08/2017 for 5000 $.

- Second line: some bitcoins were purchase at Lykke exchange for 5000 $ which represent 1.85 BTC. At the same time than this line was filled, the first line exit section was filled. Once the first line exit section was filled for the entire volume, the first line was not anymore highlighted in pale-blue which mean that the operation is 100 % completed (no more volume open).

- Third line: Some bitcoins were received in a new exchange: Bittrex. Bitcoins received: 1 BTC. At the same time than this line was filled, the second line exit section was PARTIALLY filled (only exit 1 section).

- Fourth line: Some bitcoins were received in a new exchange: Poloniex. Bitcoins received: 0.85 BTC. At the same time than this line was filled, the second line exit section was PARTIALLY filled (only exit 2 section).

Once the second line exit 1 and exit 2 section were filled for the entire volume, the second line was not anymore highlighted in pale-blue which mean that the operation is 100 % completed (no more volume open).

- Fifth line: 16 K Civic coins were purchase at Bittrex in exchange of bitcoins. They were partially sold for a good profit on the 13 of August. At the same time than this line was filled, the third line exit section was PARTIALLY filled (only exit 1 section).

- Sixth line: When the fifth line exit section was filled, then the sixth line entry section was filled to track that some Civic coins were sold for some bitcoins.

- Seventh line: a withdrawal of BTC was done. At the same time than this line was filled, the sixth line exit section was PARTIALLY filled (only exit 1 section). You can notice that the seventh line volume is negative to track the withdrawal. Negative figure is used only for withdrawal.

Instead of having directly a withdrawal in line seven we could have had first a transfer of BTC from Bittrex to Lykke. Then once BTC received at Lykke we should have converted our BTC to fiat currency (e.g: USD). Once done a withdrawal to our bank account.

You can also check how those transactions are reported and tracked on the first tab.

You can also check how the historical infos were filled to track the history in time of those operations.

[Geko]

The tab “30-Market-Analysis” is used to follow the market cap and compare it with our account growth:

b1.png

The cells to fill are highlighted in pale-yellow.

The idea is to input month after month the Global market capitalisation, the Bitcoin cap and the Ethereum cap in order to get the other altcoins cap and to see their weight and how they evolve.

Those info’s are used in the first tab.

[Geko]

The tab “20-Crypto Analysis” and “21-Crypto Analysis Bis” can be used to perform some projections or to track and follow some cryptos for further investment.

The other tab in black are used to get the information’s from coinmarketcap api.