Google Sheets API

Investing and Markets 📈 Portfolio Management ⚖️
1

This is a long shot…but wondering if anyone has had any experience with the Google Sheets API.

I would like to dynamically plot a line chart, by taking data from a cell (the value of my holdings) on a 24hrs basis, putting that data into hidden sheet.

the plotting a graph with that data on another sheet.

Anyone know if that is possible of has done something similar?

1 Like
2

You should be able to do this using “Time-driven (clock)” Triggers and with App scripts. Just be careful, as they charge for this on a per-use basis, although for 365 runs a year it should be free or negligible.

3

Thanks @saf ill take a look.

Stuff like this could be decent if FT released an API or just a web version :slight_smile: with more detail than the app…

4

I’ve previously done exactly this with a script. The portfolio value got calculated from the list of holdings and then then script recorded the portfolio value in a new row every day at 11 pm then you can plot the numbers. I still have a Google sheets template if you’d like to have a look, you have to give third party script permissions though.

3 Likes
5

Yes please David this would be a massive help!!!

If you could show me the script too that would also be handy

6

Here is a link to the Google sheets template. This is not my portfolio just an example.

https://docs.google.com/spreadsheets/d/1jLx1Fl27_jag4o_rKUrZBiwVECXThO2doN5YoTlKHOw/edit#gid=1495977050

If you go to the script editor in the spreadsheet you’ll see the code below. When you run it manually you’ll see it adds another row to the history tab (row number to use is on A1 in history tab if you need to reset it). After that you’ll need to set up triggers (button highlighted in picture below), time driven > day timer > 11 pm or something like this.

I’m on the Discord channel if you have any questions, just @DavidM me

image
image1027×524 66.4 KB

4 Likes
7

Amazing…ill give it a go…added a trigger to it already, now I just I have to get it to work LOL…

FYI there are two DavidM’s on Discord

8

You can also create triggers programatically, looking at your code you would need to add something like:

function createTimeDrivenTriggers() {
    ScriptApp.newTrigger('portfoliovaluetracker')
        .timeBased()
        .everyDays(1)
        .atHour(23)
        .create();
}

Then Run this function. It’ll ask you for permission to modify your setup, and then it will take you through a oAuth process.

1 Like
9

Thanks for the help guys.

I have got this all working as intended. :smiley:

2 Likes
10

Does Freetrade supply any kind of automatically updated Google Sheets, similar to the one on Monzo plus?

If not, that could be a nice thing for Alpha

1 Like
11

@DavidM interesting!

I’m just getting into App Scripts (and posted this on the community Portfolio tracking spreadsheet - what metrics are useful to have?).

It would be great to be able to have a look at the Google Sheet template that you mentioned in the post but the link does not work any more. Do you have an updated link by any chance?

I’d really appreciate it :slightly_smiling_face:
Dasha

12

Hi Dasha, sorry I don’t have this sheet anymore. I have another sheet I use for something else with the script if you have any questions.