Track your Stock and Crypto performance with Google Spreadsheets

Masumi Mutsuda

If you own stock, crypto or you invest in index funds I’m sure you want to keep track of your portfolio performance. There might be some apps out there that can do the job for you, but sometimes they don’t have all the features you need.

Google Spreadsheets are a powerful tool for the task because they are versatile, easy to use and can be superpowered with Google Apps Script.

Populating the sheet with your portfolio

The first step is to structure the information of what you own into rows and columns. I like to separate all investments in a sheet per category.

Create one sheet for each category

Stock sheet

In this sheet we will create 4 columns.

Company: The name of the company you own stock from.

Code: The code the company is represented within the stock market.

Titles: Number of titles you own from that company.

Value: This field will be automatically calculated from the current value of the company and the number of titles you own.

=C9*GOOGLEFINANCE(B9)*GOOGLEFINANCE(“CURRENCY:USDEUR”)
Detailed explanation of every element in the formula

Index funds

In this sheet we will create 4 columns. There is no GoogleFinance formula for index funds so we will have to get updated values manually.

Fund: Name of the index fund.

ID: Morningstar ID code of the index fund (you can get it by searching for the fund in https://www.morningstar.co.uk/ and taking the last string from the URL.

Shares: Number of shares you own.

Value: Value of each share. This field will be automatically generated from the current value of the index fund using the morningstar webpage.

=index(split(index(importhtml(concat("https://www.morningstar.es/es/funds/snapshot/snapshot.aspx?id=";B2);"table";4); 2; 3); " "); 1; 2)
Detailed explanation of every element in the formula

Amount: This value will be the result of multiplying the number of shares you own by the automatically calculated value. (Value * Shares)

=C2*D2

Cryptocurrency

In this sheet we will create 3 columns.

Coin: The name of the currency you own.

Amount: The amount you own.

Price: The current price of what you own. This field will be automatically calculated from the current value of the coin.

Use the BTC to EUR conversion for BTC and ETH to EUR converstion for Ethereum

Final touches

At the top of each sheet on a new column we will automatically calculate the total amount we have invested.

We calculate the sum of our investments

We will finally create a new Sheet called Dashboard, with three columns, one for each of our investments, and will reference the total amounts in the other sheets.

We use SheetTitle!SheetCell to access data on other sheets

Done! Now you have a fancy dashboard where you can check your updated portfolio at anytime and from anywhere.

Take a look at the live verison of the Spreadsheet I’m using as an example.

来源

What do you think?

发表评论

电子邮件地址不会被公开。 必填项已用*标注

Loading…

0

Comments

0 comments

Reflecting on DDEX in the age of flash loans

Lightning Network Channel Balancing with ThunderHub