scripts for calculating a multicurrency basket

Everyone dreams of becoming a little richer — to go back to 2011 and buy bitcoins for the entire scholarship, so that today they can be a respected person. Is it possible? Most likely not. But nevertheless, if we want to save money for an apartment or a comfortable old age, we ask ourselves: in which currency or shares is it promising and safe to store? The simple answer is none. If we look back into the past, any national currency loses its value, and shares either rise or fall, as does cryptocurrency. It is impossible to accurately predict how a specific currency will behave in the future.

I will not make my forecasts, since I am not an economist, but I will show how to make a tool for calculating your assets between different financial instruments. And I made it from Google tables and AppsScript, I needed the latter to find out the current rate.

What is this all for?

I'll give you some context on why I decided to write this article, but first a few words about my background. I currently work for M2, an online platform for solving real estate issues. It's trendy to be a multipotentialite now, and it seems like that's me. I'm both a Product Owner and a head of the interface development team. My team consists of full-stack developers, our stack is based on Typescript, as well as: React, Gatsby, Nest, Next, Playwright, and we're open to everything new. The essence of our project is to make an admin panel, which is basically a CMS and an advertising management system.

So, why did I decide to write this article? While traveling around different countries, I realized that I don’t like winter at all and want to have a second home in a warm country, so I started saving up for my dream little by little. I wanted to save up effectively, so I started doing it in different currencies, including cryptocurrencies.

To keep track of all my assets, I found nothing more convenient than Google Sheets and initially used them primitively. But one day, our team faced a task that was easy to solve using Google Sheets and AppsScript — by the way, Anastasia Sergeeva will write an article about this a little later, but now I will tell you how I simplified my life. AppsScript is an application development platform that allows integration with Google products and is based on modern JavaScript.

Even if you don't have programming skills, you can copy a Google document to yourself, fill it with your data and use it. And if you also know how to code, you can write an assistant for yourself from scratch or attach additional financial instruments, such as shares, by connecting scripts to the stock exchange.

How I packed my basket

I assumed that I would save in a freely convertible currency, as the Central Bank did, and put together a bi-currency basket by analogy: 45% euros and 55% dollars. But the currency is constantly falling in relation to real estate. It is difficult with shares because of sanctions, so cryptocurrency remains. You can see here top 100 currencies by capitalization and choose what you want to dilute your savings with. I'm not a big expert, but I don't believe in Bitcoin – it has slow and expensive transactions, it can't be used for your own projects. USDT and USDC are essentially the same dollars, but without government guarantees. I chose Ethereum – it supports smart contracts and is the most stable, since it is already old. And also TON, a competitor to Ethereum, it is new, undervalued and there is a chance that it will grow faster than the market. Its wallet is built into Telegram, transfers can be made to contacts directly. In general, I believe in it, despite the fact that the bulk of this currency is concentrated in the hands of a few investors.

So the basket is: 22.5% euro, 27.5% dollars, 25% Ethereum and 25% TON. This is all for example, you can distribute as you wish and even add bitcoin to the basket, to tell the truth, I have other assets myself, but I don’t want to complicate the article, especially since it’s not about that.

Everything you need in one table

There is no tool that allows you to store all types of assets in one place, and it is not safe. As the saying goes: “don't put all your eggs in one basket.” However, I realized that it is important for me to see the current state of the basket for all assets. Namely: I want to see the cost and quantity of each asset, as well as the total cost. All exchanges and wallets allow you to see the total amount, and I was inspired by them when designing the table. However, firstly, there is no service that works with fiat and crypto assets at the same time, and secondly, I do not even store crypto assets in one place, some on one exchange, some on another, some on one wallet, some on another.

At the top of the table I added the currency code, right below it – the quantity and rate in different convertible currencies for clarity. Below is the cost of the asset in the basket, it is calculated using the formula. On the gray background is the variable data: the cost of the asset is recalculated automatically when the quantity changes.

Automatic currency rate updates

The quantity is set independently, but the rate should be received automatically, so as not to enter it constantly, especially since four assets in three currencies are 12 (10) constantly changing values. And here Apps Script comes to the rescue.

Go to the Extensions/Apps Script menu and a convenient script editor opens, JavaScript v8 is used there. So, the first thing I wanted was to write a function that would get the exchange rate of one currency relative to another. I used the API of the Central Bank of the Russian Federation and wrote the following function:

function cbrRate(charCode="USD", charCodeIn = 'USD') {
 try{
   const url="https://www.cbr-xml-daily.ru/daily_json.js";
   const response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
   const json = response.getContentText();
   const data = JSON.parse(json);
   const currency = data.Valute[charCode];
   const returnCurrency = (outC, inC = {Value:1, Previous: 1})=>{
     const currentRate = outC.Value / inC.Value;
     const previousRate = outC.Previous / inC.Previous;
     return [currentRate, (currentRate - previousRate)/ previousRate];
   }
   if( currency ){
     if(charCodeIn === 'RUB'){
       return returnCurrency(currency);
     } else {
       return returnCurrency(currency, data.Valute[charCodeIn]);
     }
   }
 } catch(e){
   console.error(e);
 }
 return null;
}

The Central Bank of the Russian Federation returns the rate in rubles, so I made various conversions to convert the rate from one to another. Later I needed the previous value of the rate, so I return an array:

1. Current rate

2. Previous course

The Central Bank of the Russian Federation knows nothing about cryptocurrencies, but I found a couple of services that return rates in currencies, I will give one of the functions as an example:

function tonapiRate(charCode="USD", charCodeIn = 'USD') {
 try{
   const tonURL = `https://tonapi.io/v2/rates?tokens=${charCode}&currencies=${charCodeIn}`;


   const tonResponse = UrlFetchApp.fetch(tonURL, {'muteHttpExceptions': true});
   const tonjson = tonResponse.getContentText();
   const tondata = JSON.parse(tonjson);
   const ton = tondata.rates[charCode];
   if(ton && ton.prices[charCodeIn]){
     return [
       ton.prices[charCodeIn],
       parseFloat(ton.diff_24h[charCodeIn].replace("−","-"))/100
     ];
   }
 } catch(e){
   console.error(e);
 }
 return null;
}

And to finally not think about which API the courses are taken from, I wrapped these functions in the following:

function exchangeRate(charCode="USD", charCodeIn = 'USD') {
 return cbrRate(charCode, charCodeIn)?.[0] || tonapiRate(charCode, charCodeIn)?.[0] || coinbaseRate(charCode, charCodeIn);
}

That is, if one cannot return a value, it looks at the next service, etc. Subsequently, it will be possible to expand the function with other different APIs. The rates are slightly different on different resources, but this is not so important to estimate the cost of the basket.

How the exchange rate and value of an asset changes

Next, I wanted to watch the change in the exchange rate and the value of the currency in the basket. For this, I needed the second value of the function. The Central Bank of the Russian Federation and the TON API have different meanings: the first shows the price of yesterday and the day before yesterday's closing, and the TON API simply shows the price 24 hours ago and the price of the last transaction. However, this is also not very important for the assessment, we are saving for the long term.

I wrote the following wrapper:

function exchangeDiff(charCode="USD", charCodeIn = 'USD') {
 const cbr = cbrRate(charCode, charCodeIn);
 if(cbr){
   return cbr[1];
 }
 const ton = tonapiRate(charCode, charCodeIn);
 if(ton){
   return ton[1];
 }
 return null;
}

And I made a convenient conclusion in the table, to add a function call to the table you just need to write in the cell:

I used conditional formatting: if the rate is rising, I show the difference on a green background, if it is falling, then on a red background:

To keep the weights up to date, I wrote them down and calculated the current asset allocation underneath them:

And the total number

Notification via Email

You can easily connect the Gmail service to add various notifications, for example, based on the condition that another record has been broken, here is an example of the code:

if(sumInBaseCurrency > max){
   GmailApp.sendEmail('mynameissergey@gmail.com', 'Congratulations', `Your balance has reached €${sumInBaseCurrency.toFixed(2)}\n for €${(sumInBaseCurrency - max).toFixed(2)} more`);
 }

The script can be called by a trigger, for example, once an hour or less often, depending on how often you check your mail.

Try it yourself

To use my development and see how everything works,

• open link

• copy to your space, fill in your weights and quantities

To add a new currency, simply add its code to the title: rubles — RUB, yuan — CNY, bitcoin — BTC, Solana — SOL, etc.

Results and how the tool can be further developed

In the article, I shared my case study on how to quickly throw together a script that helps monitor the current state of a basket of fiat assets and cryptocurrency. You can customize it for yourself: change currencies, add new APIs, notification formats, etc.

What's next? Functions can be called not only from the table, but also formatted as GET/POST API. There is an idea to connect a controller with a small screen, like T-Display-S3, to this table, hang it on the refrigerator and monitor how close I am to my dream every breakfast, lunch and dinner. And you can also monitor the rates of the main assets, but more on that in the next article – you need to dig into your knowledge and remember the C and C++ languages.

You can change tables, access individual sheets, call functions by trigger. You can store historical data on rates, track how much, when and with what instrument the asset was replenished. But that's another story!