How to manage your crypto portfolio

How to manage your crypto portfolio
Google Sheets
·
8/5/2022
·
10 min. read

Written by: Kseniia Fedorinina, founder of Lucidnode

Ups and downs of crypto-destiny or how to manage portfolio in a bull run

In this article, I want to share my experience of automating a crypto portfolio - in particular, how to manage it. There won’t be any tips on how to find another gem or how to invest $100 and ride off into the sunset in your own Lambo a year later. I’m just sharing the tools I use to manage portfolio incomes, exits and my small crypto ETF.


This article is not investment advice. The actions described in this article and portfolio structure are not backed by anything but the author’s analysis. The author is not a pro trader or investor. Do not invest more in crypto than you are willing to lose. Do not invest in crypto if you are prone to panic and drama. To tell you the truth, if you don’t have crypto investments yet, you may stop reading :)


Last September, in the middle of an epic bull run I decided to break into the crypto world. Admittedly, it wasn't the best time to do this, but my strong feelings of FOMO took over . The market grew incredibly rapidly. People around me told stories about their mom's friend’s son, who invested in Solana and then, a year later, bought a private island. Some portfolios grew 100x in just six months. There was a feeling that any new crypto project would turn into gold.

I flew into this brave new world at full speed, without thinking where to go. I immediately started investing across-the-board: long-term investments, speculation, IDO-platforms, airdropsand whitelists, staking on DEXand CEX, launchpad and launchpool on Binance, farming, purchases, God forgive me, NFT-lands and other mad ideas. I also set up at least six or seven crypto wallets and the same number of accounts on different exchanges.

In just a week, I realized that I needed to streamline my growing list of assets, or else I would simply lose control. I also wanted to understand how much my portfolio is worth and to track the amount of income and losses. However, the main thing was to analyze this information for each token separately so that I could either exit it in time or buy more. I began to study the tools available on the market, but none of them covered 100% of my needs.

Portfolios in CoinMarketCap.com or CoinGecko.com might seem convenient, but they are not ideal for me. Here is my list of cons:

  • Form of tables: some parts have an information overload and vice versa. For example, suppose I receive some tokens as a form of interest on staking - namely for free. Thus, I want to see them in a separate column - they warm my heart :).
  • Lack of space for notes: I need more fields to enter info on the project, my account in it, and my strategy. I need these notes in a table, not inside a transaction.
  • No flexible profit display format, only percentages.
  • For storing information on projects which have not yet entered the exchange, these tools are not suitable at all. They simply don't let you add a project without trading pairs, let alone all sort of retro airdrop, where you don’t even know the name of the future token.
  • There is no summary data for multi-portfolios: I collect different portfolios and want to see detailed and general statistics with profit-losses, Xs and other goodies.
  • In general, I prefer to work with tables. You can always improve a table according to your needs: add new sheets or fields and/or link them to the report you need, when built-in charts are not enough.

Finally, and most importantly, what will I do if something happens to this portfolio? Just the thought of it makes me shiver.. How can I expect any guarantees and reliability from a free beta tool, which is not yet the main source of traffic for these sites?

I decided to look under the hood (go deeper), and I found a couple of nice apps like Delta.app. In Delta, it is possible to add your own tokens that have not yet entered the exchange - a very convenient feature for active IDO participants. However, there is the same problem with flexible field settings.

Then I looked towards more complex tools, like TradingView.com. These are fully-fledged products for professional traders, where the probability of data loss tends to zero. I found them overly complex. I don’t trade - I don't advise you to trade either - and I absolutely do not need all these complex charts, graphics and technical outputs. But what I do need is flexibility in table settings and notes, which are, sadly, not perfect there either.

This is how I decided to make a tool myself: Google Sheets + Albato + a bit of creativity. I started by creating a table for my portfolio and pulling up the current rates for my assets.

I’ve chosen CoinMarketCap.com as a source of data on exchange rates. It has an open API, with detailed documentation and a fairly large volume of transactions on a free plan. Anyone can get access to it by registering** here** . Albato did not have a ready-made integration with this system, but the Albato team helped me to quickly set it up on my own, using an Apps Integrator (no-code builder, more details - ** here** ).

As a result, I got a super-handy table that covered 100% of my needs.

unnamed.png

You can find my table here - google spreadsheets . The screenshot does not show the right side of the spreadsheet with columns for fixing purchases. I entered several projects, with different rates, so in the summary section I can see the average cost of entry and the amount of money I spent.

Like every novice crypto-investor, I was checking my coins every couple of seconds. Since it is inconvenient to use a large table on a phone, I created a convenient mobile tab for myself.

unnamed (1).png

Of course, a table like this can be made on any free portfolio website, but it is undoubtedly more convenient when all the data is in one file. During the autumn bull run, I opened this spreadsheet every morning and saw an increase in all positions by 15-20%, then opened the next tab to marvel at the huge amount of money that I earned overnight.

Step-by-step instructions for creating a portfolio table

  • Sign up to CoinMarketCap.com to access the API. Choose a free plan, go to your personal account and copy the API key.

unnamed (2).png

  • Sign up to Albato.com. Choose a plan according to your needs.
  • Follow the link and get into the template I have created. In the CoinMarketCap drop-down list, click the "Add connection" button.

unnamed (3).png

  • Paste the API key you copied from your personal account on CoinMarketCap

unnamed (4).png

  • The connection is now ready. Along with the settings, we’ve got a list of all tokens available on CoinMarketCap and their internal ID. This list is not updated automatically, so to speak. You will need to update a connection in order to add a new token entered into the exchange and its ID.
  • Click the "Continue" button and go to the next step. Here, we’ll see a single field. Choose any value. If the field supported the multiple selection option, we could select all the tokens we want to get statistics for. We will set up a full list later.
  • By adding the token, we automatically create the first automation. But we still need to set up a couple of things. The first is to specify the full list of currencies for which we will receive data. To do this, click on the gear icon in the CoinMarketCap step.

unnamed (5).png

  • Next, click on the “Currency ID” field and select currencies from the drop-down list. The search is performed by the token's name, since there are many duplicates if searching by symbol. That is, bitcoin can be selected by typing in "Bitcoin". Lifehack: you can search for currencies through the search bar and add multiple currencies in one field by holding down the “Shift” key and clicking on the currencies. Save the list.
  • Next, you need to add a connection to the last step - "Google Sheets", and specify the spreadsheet which the data will be transferred into. Click on the down arrow and click the "Add Connection" button:

unnamed (5).png

  • Provide Albato access to a table previously created in Google Sheets.
  • Then we set up a schedule for receiving data. Go to the Start Schedule section.

unnamed (7).png

In the Request Frequency field, select the frequency of receiving data. Do not set updates too frequently, as both Albato and CoinMarketCap have a limit on the number of requests on free plans. You can calculate these limits yourself and see if this frequency is enough for you or whether you want to receive more up-to-date info and pay extra money for it. I personally pay :)

  • Depending on the schedule you set up, raw data from CoinMarketCap will be sent into the selected Google spreadsheet. I would advise you to pre-set the column names in this sheet to fill in the table from the second row, so you can sort out the data. As a result, you will get a sheet which looks like this. This will be filled in automatically, each time overwriting the existing rows, except for the headers.
  • Raw data from this sheet needs a little pre-processing, since Google doesn’t read formats of percentages and prices as numbers. On a new sheet, I remove commas, then I convert the data to a number. Here is the formula template . An important thing is that this sheet does not load new rows automatically. If you add a new token, you need to write its ID on this technical sheet and set up all the formulas yourself.
  • Done! Now you can receive the current prices and their trends from your portfolio. Next you just need to transfer them to the main tables using VLOOKUP. My template already has all of the formulas. Please note that on the sheet for the mobile version, the first column with the ID is hidden (to improve the ease of reading). To add new rows, you need to display it.

How to add new tokens

  • Sign in to your Albato account, go to the Automations section, pause your automation, click on the settings icon on the automation card. Then click on the settings icon on the CoinMarketCap step and add new tokens. If you cannot find a currency in the list, it means that it is not in a directory yet. Go to the Apps section and update your CoinMarketCap connection to add a currency in the directory.
  • Wait for the new currency in your raw data spreadsheet, and copy its ID.
  • Add this ID in a new row on the technical sheet, on the sheet with the list of coins and on the mobile sheet (displaying the first column) and set all the formulas. Then everything is ready!

Automatic notifications

Having the illusion of control, I felt a burst of investment energy and decided to move forward in the world of crypto automation. The idea was born after investing in the well-known SHIBA token. Naturally, I made all the mistakes of a novice investor and joined the shiba-army. This was my second investment in crypto. I had heard enough of some armchair crypto-analysts on YouTube, watched five or six videos with “super secret” rumors about the future of Shiba and believed that an incredible event would soon happen (either burn or exit to Robinhood) and Shiba would, therefore, go to the moon.

I bought Shiba for $100 and waited for this news. “I’m a smart investor,” I thought (ha-ha). “I won't just believe any so-called crypto expert on YouTube. If the price increases, it will mean that their forecasts come true and I can buy more. But how do I know that the price is increasing? I can't check the rates every second. I need Telegram notifications!”

In Albato, you can set up a Telegram notification about value changes, with flexible configuration (growth, fall, percentage). And so, I got down to business.

Step-by-step instructions for setting up a bot

  • Sign in to your Albato account, go to the Automations section. Stop your automation scenario and go to the Automation builder. Click on the + under the Google Sheets step. Go to the Tool section and choose the Iterator tool from the list.

unnamed (8).png

  • There is only one field in the setting - select the “Get cryptocurrency quotes” option from the drop-down list, and click the Save button. This tool will allow us to parse the array from CoinMarketCap into separate elements - rates of one currency.
  • Now we need to set a condition. Click on the + under the Iterator step and the Stop automation on a condition tool. In the settings, we choose what will happen when the condition is met (we need to configure the tool so that the automation doesn't stop). Pick the “continue automation” option, and set up a condition for sending Telegram messages.

An example condition could be an increase in the price by 3% per hour. The token we need can be selected by its ID. In the first field, select “ID iterator”, equal to token ID, which can be found in our table. Let me remind you that all the fields in this drop-down list are the data that we receive from CoinMarketCap. Click on “Convert to a group of conditions”, choose the “And” switcher, add a condition of growing by 3%: “quoteUSDpercentChange1h” >= 3.

unnamed (9).png

Using the “Add a condition to the group”, you can set up triggers for all the tokens you need.

  • Now we need to set up a Telegram step. In the Automation builder, click on the + and select go to the Action section. Select Telegram in the first drop-down list, specify the "Send a message" action and provide access to your Telegram account.

unnamed (10).png

IMPORTANT! After providing access to your Telegram account, you need to activate the AlbatoBot bot with the /start + token message (token is in the Apps section). Instruction.

  • Click the Select button and go to the message text settings. We can set up the text with a list of fields from CoinMarketCap and static text. For example:

unnamed (11).png

  • Click the Save button. Done!

In fact, if you don’t want to spend extra operations on your Albato account and are happy to adjust some settings manually, Telegram has ready-made bots. For example: @DSPriceAlertsBot.

This one is good because it connects to a huge number of DEXs - in fact, it has almost every trading pair on the market. It also has a convenient setting (by crypto-world standards).

unnamed (12).png

But there is a serious drawback - when a notification is sent, a trigger is removed. That is, if you need constant notifications about a 1% bitcoin drop in the last hour, you need to set up this notification by yourself again after every time it has been performed.

So, I would recommend using this bot for one-time tasks. For example, let’s say I want to buy a particular token for my portfolio when its price drops below $10. I set up this bot, receive a message and buy the token.

On the other hand, if I need constant or repeated triggers for some token, I use Albato. Here I don’t need to go through the process of setting up notifications each time. Constant notifications are useful for the following tasks: – to worry more often about my assets falling (ha-ha) – to control the news background (for example, I know that the event X will happen soon and it will seriously affect the price of some tokens). The token’s price jumps around a lot, so I go to read twitter for news about it. – to indirectly control listings (listing on a cool exchange always pumps the price), they often give all sorts of limited promo offers for staking and I manage to profitably invest my asset. – to make decisions about exits (for example, I want to sell a token when the price X is reached).

PS: If you are interested in the story with Shiba, the story did not have such a happy ending :) Late one night, after receiving a growth notification, I hurriedly bought some more. Not at the highest level, of course, but close to it. Since then, Shiba has halved, and I am sitting on a pretty big loss.

But the very idea of these notifications helped me a lot. For example, I once managed to sell a token - which I really wanted to sell saving at least the purchasing price - at this peak:

unnamed (13).png

This story took place during a relaxed evening, in the company of friends and wine. Had it not been for Albato’s message on Telegram, I would not have had time to sell this token. An automatic order on the exchange would not have worked, since it was just above this peak.

Necessity is the mother of invention or how I made my crypto-ETF

You've definitely heard the stories about the eagle-eyed investors who started entering S&P500 ETFs using the DCA strategy ten years ago and have already saved up for a comfortable retirement. The essence of the idea is to invest regular amounts every month into a globally growing asset, which smooths out price volatility and big jumps over a long period of time. The idea is proven, and it’s as old as time itself. A pleasant bonus of this strategy is that you do not need to be good at each project, you buy them in a pack, which saves time and doesn’t shred your nerves.

But where can we get crypto ETFs? I searched for a similar product on top trusted CEX exchanges, but found nothing. Further searches showed that many traders and investors collect it themselves. There are also offers for trust management. But the crypto world is not a place to trust your money for management if you do not trust this person 100%. So I decided to make this product myself.

Just keep in mind that this is not a real ETF, it does not contain 100% of the assets of the crypto market. Firstly, now there are more than 20,000 projects in the crypto world. Furthermore, it is impossible to automatically buy all of them, because many are traded on the most bottom exchanges, which have neither APIs nor a web interface. Secondly, 95% of these assets are scams which will not even exist after ta year.

I decided to focus on the TOP50 projects by capitalization, excluding stablecoins , gold-backed tokens and other stable things. It is difficult to get into this list (although there is Shiba, ha-ha) - projects are seriously tested by time and analyzed by many members of the crypto community. Well, this TOP occupies almost 95% of the total volume of the crypto market, so the rest can simply be neglected.

Another important difference from the classic ETF is that I buy tokens not in proportion to their contribution to market capitalization, but in equal amounts - otherwise, my portfolio would be 70% Bitcoin and Ethereum. This will give me a well-diversified portfolio, but with potential for a big profit too. After all, someone inside this TOP50 can jump up from 49th place to 3rd. Then I will earn enough not only for a Lambo, but also for a plane. In a hundred years, lol)

The last difference is that I continue to buy an asset even if it has left my TOP index. This happens occasionally, as the coins in the lower positions can be quite volatile. If you do not continue the purchase, it will turn out that you bought the token at the highest price. Continuing these purchases performs the “ladder”, so an unsuccessful entry is smoothed out by a cheaper one. I decided that once every six to eight months I would audit and analyse these lower-value coins. If the tokens continued to lose positions, it would be necessary to study the project. With the help of some detailed analysis, it is possible to figure out whether this token has a chance to go up or if it needs to be sold to fix the losses.

Of course, the ETF portfolio also needs automation. You need to regularly receive a list of TOP projects with quotes. The previous template I created in Albato doesn’t work because it makes API calls that request a list of specific currencies. I found another report on CoinMarketCap - TOP list of currencies by capitalization. This fits the bill perfectly!

Next, we set up a new template for this method in Albato. You can find it here.

Step-by-step instructions to setting up ETF tracking

  • Select the “CoinMarketCap” and “Google Sheets” connections that have already been created. You just need to pick a new sheet for the data and save the settings:

unnamed (14).png

Click on the "Continue" button and start the automation to transfer your TOP100 into the spreadsheet. This template has the “Get listing updates (rows) - multiple transactions” event. This event lets you set up a filter for each token, but it will use up more operations. In case you don’t need a filter, you can replace it with the “Get listing updates (rows) - 1 transaction” event. This will reduce the number of operations.

  • When the TOP100 rating is transferred to the selected tab in Google Sheets, the raw data looks like this.
  • Next, go to the technical tab to convert the formats.
  • Create your worksheet with the history of ETF entries, amounts and quotes for the portfolio.
  • Buying each month, I combine the list of my portfolio with the fresh TOP62 on a separate sheet highlighting duplicate entries, so I can find new tokens that have entered the TOP. This task could surely have been solved with formulas, but I was too lazy to bother with this) The whole procedure for updating the list takes 1 minute. It is more difficult if something from the portfolio forks (as BTT did). It took me quite a long time to understand what happened to the token and how to paste new data into the table.
  • The Binance exchange has all the tokens I need from the TOP, except for a couple of positions. I buy them on Gate.io. Binance has a very convenient buying process. But you can choose any other exchange, just check on CoinMarketCap, where the token is traded.

After monthly purchases, I try to place new assets into all sorts of financial instruments on Binance - staking (fixed and floating), loans, etc., so that all these coins can also multiply in volume. Sometimes it turns out that it’s possible to invest at a very good interest rate. Recently, I’ve had AXS at 104%. But this rarely happens. You have to chase after good staking terms. Therefore, I track all the sections at least once a week in order to shift something at a better percentage. It is also important to remember that some offers have a short investment period (10-30 days).

This doesn’t involve as much fuss as it might seem at first glance - it takes me just an hour a week. But it gives me the chance to significantly increase the number of purchased tokens, especially in the long run.

Let me remind you once again that everything I’ve written is not financial advice, a guide to action, or a strategy that works 100%. All these portfolios can turn into dust and ashes. DYOR.

Gaming and other portfolios

In 2021, NFTs experienced a rapid increase in popularity, but I was late to break into this (and thank God). In 2022, gaming and the metaverse are trending (thanks, Mark). So I decided to build a portfolio containing a few entries of promising projects in this area. Now, of course, this idea does not sit so well with me, because projects from the gaming industry are very sensitive to market sentiments. This portfolio is now showing the strongest decline. And, if the bearish mood in crypto continues, gaming projects may simply not make it through. Not just drop in price, but go completely bankrupt)

Well, on the other hand, I see my losses on this portfolio in real time (ha ha). I put the list of these projects on a separate sheet and pulled up the price data through Albato. The scheme is exactly the same as in the first part of the article. I just add the necessary projects in the automation settings in Albato. Data is sent into the general sheet, then to the technical sheet and from there to the gaming tab. This tab is a little bit different: I make some notes of the project's industry, which will help me remember why I bought it in the first place. By following this, you can create a portfolio on any topic.

Other goodies

I also have a sheet for accounting exits. If I exit any asset, I delete it from the previous list and add it to a separate sheet, fixing the entry and exit details, profits and losses. It can be a pleasure in itself to go to this list and appreciate your foresight, because most of these assets have fallen significantly in price. I haven’t done an automatic pull-up of quotes for this sheet. By the way, I can create an Albato automation scenario with weekly updates (to save operations) and enjoy reading my summary data on the potential losses I didn’t suffer ;)

There are IDOs in which I participate. There I keep track of projects purchased on sales (before listing), entry price, vesting, claim detailsand X's. Not all projects can be sold immediately, so it's better to track all your exits from them and compare the data with the current price. Perhaps some of them should be held until better times.

Conclusion

Furthermore, this table can be developed according to your needs. For instance, I once built graphs and charts on the database. I tagged each project according to its theme, ecosystem and reliability rating, and looked at the shares in the portfolio. But it didn’t work - I am, after all, a man of tables. However, some of them may be useful - for example, to see if you have a misalignment to one network or if there are too many projects in the portfolio that tackle the same problem (e.g. liquidity protocols). You can also immediately calculate the exit price for each project and set up triggers for it. This is what I did, but now I am as far from these prices as possible, so I can’t tell you anything about the usefulness of this idea, hah)

The table helped me cope with the feeling of panic that I was missing some important nuances. It's probably more about the inner feeling of control, but still, I began to sleep more calmly. There’s also another bonus - in the event of my premature death, my loved ones will be able to understand where their inheritance lies - this is also a reassurance))

PS: Under no circumstances should you store the addresses of your wallets and private keys in such tables. This format is only suitable for portfolio statistics.

@Kseniia Fedorinina, founder of Lucidnode


Google Sheets