So I just discovered Google App Scripts

Life Hacking
1

Was I the last one on earth that discovered the google app scripts to automate spreadsheet stuff?
I used a similar approach to this one:

And now my spreadsheet is finally 99% automated. I retrieve all ETF value (either with the ticker or with importhtml for VIAC) and when the asset allocation is unbalanced over a certain threshold the script send me an email “Rebalance opportunity!” to me.

I used to randomly check my spreadsheet 2-3 times a month. Now I don’t even need to do that! If something happens to the market and my asset allocation goes out of balance (I don’t follow the market) I receive an email with a summary of my situation, my stock balance and my etf distribution and I can decide to act or not on it.

The only thing left to automate fully would be to retrieve the number of shares of my VIAC ETFs. But I guess I can simply write them once and in most of these accounts they rarely change (difficult to trigger VIAC condition for rebalancing).

3 Likes
2

Sounds like you’ve put quite a few interesting things into place! Would it be possible to share what you did for VIAC with importhtml?

1 Like
3

Oh I see, it’s probably this: Where do you get your data feeds from? - #29 by Cortana

1 Like
4

First of all nice job… I am also into programming/automating the management of my assets. Regarding VIAC I have been in touch with them recently in order to ask if they can provide a CSV export of all transactions, they said they would take this into account but of course with no guarantee. VIAC only offers to download their PDFs which after a few years is much too much PDFs. Finpension for example provides a nice simple CSV file with all transactions and that would be much easier to use.

1 Like
5

Why not an API instead of a csv file. :wink:

1 Like
6

Hehe I did not want to ask for too much but yes ideally an API would be great, no need to login, so I can fully automate getting all transactions from them on a regular basis.

7

here the code to send an email automatically based on some value in a google spreadsheet cell. You need to implement triggers as well, aka when the script should run. I had setup twice a day 4 pm and 8 pm. A bit of a pain to set-it up, here google could do better. I tried to code it as simple as possible with new variable at each step

function SendEmailRebalance() {

// Retrieve Rebalance Value
var rebalanceRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Riassunto”).getRange(“E8”).getValue();

//just use two decimal number:
var rebalance = rebalanceRange.toFixed(2);

// Fetch the email address:
var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Riassunto”).getRange(“H22”);

//get the arrray of strings
var emailAddress = emailRange.getValues();

//email adress is first of string
var emailAddress1 = emailAddress[0][0];

// Prepare message
var message = ‘Your rebalance trigger activated! Rebalance amount: ‘+rebalance +’ chf.\n’;

var subject = ‘Rebalance opportunity’;

// Check rebalance, here with a fix amount you can use a percentage

if (rebalance > 2000){

MailApp.sendEmail(emailAddress1, subject, message);

}

}

hope this inspire somebody!

to do triggers this guide is great
[Use Google Sheets to Send an Email Based on Cell Value]

2 Likes