How can I use Google Sheets, Google Finance, and Sparkline to create a networth chart based on my current holdings?

Question

Right now I am using a google sheet that keeps track of my stock investments. What I would like to do is methodically calculate what my networth would be over time given my current holdings. Obviously this isn't a true historical networth but it helps to give an idea of how my portfolio has trended over time.

From what I have so far I can use a function like

=GOOGLEFINANCE("AAPL","price",TODAY()-5,TODAY(),"daily")

in one cell to generate data like the following

Date                Close
8/24/2020 16:00:00  503.43
8/25/2020 16:00:00  499.3
8/26/2020 16:00:00  506.09
8/27/2020 16:00:00  500.04

What I want to figure out is how to easily combine this data with other formulas that look the same for different tickets.

What I want:

    =(GOOGLEFINANCE("AAPL","price",TODAY()-5,TODAY(),"daily")+GOOGLEFINANCE("BA","price",TODAY()-5,TODAY(),"daily"))

    //Apple was mostly 500ish and Boeing was around 170
    Date                Close
    8/24/2020 16:00:00  681.7
    8/25/2020 16:00:00  674.03
    8/26/2020 16:00:00  677.99
    8/27/2020 16:00:00  674.24

Another part of the questions is how do I manipulate this data in general if for example I want to multiple the close value by 3?

=(GOOGLEFINANCE("AAPL","price",TODAY()-5,TODAY(),"daily")*3)
Date                Close
8/24/2020 16:00:00  1510.29
8/25/2020 16:00:00  1497.9
8/26/2020 16:00:00  1518.27
8/27/2020 16:00:00  1500.12

Eventually the goal is to have a sparkline that contains all this data using the simple formulas e.g. =SPARKLINE((GOOGLEFINANCE("AAPL","price",TODAY()-365,TODAY(),"daily")+GOOGLEFINANCE("BA","price",TODAY()-365,TODAY(),"daily")*3),{"charttype","line";"linewidth",1;"color","#5f88cc"})

The simplest answer is just make a part of the sheet where I get all the stocks and add new columns that have the addition and multiplication I want. However I want to be able to do this with 5 years of data and it would be much nicer if it could be in one formula so that my sparkline looks simple.

score 0 · Answer 1 · answered Jul 17 '21 at 14:34

Is this what you want?

=SPARKLINE(
  QUERY(
    {
      GOOGLEFINANCE("AAPL","price",TODAY()-365,TODAY(),"daily"),
      GOOGLEFINANCE("BA","price",TODAY()-365,TODAY(),"daily")
    },
    "select Col1, Col2+Col4*3",
    1
  ),
  {"charttype","line";"linewidth",1;"color","#5f88cc"}
 )

How can I use Google Sheets, Google Finance, and Sparkline to create a networth chart based on my current holdings?

1 Answers1