Search code examples
google-sheetssparklines

Add 2 sparlines in a google sheet to create a new sparkline

I have 2 sparklines. Both are created based on historical share prices of stocks from a year ago to today. I want to create a new sparkline that is the sum of these 2 charts. Is this possible given that the have the same number of data points i.e 365. e.g

=SPARKLINE(GOOGLEFINANCE("AMZN","price",TODAY()-365,TODAY(),"daily"),{"charttype","line";"linewidth",1;"color","#5f88cc"})
=SPARKLINE(GOOGLEFINANCE("MSFT","price",TODAY()-365,TODAY(),"daily"),{"charttype","line";"linewidth",1;"color","#5f88cc"})
Solution

  • You can join them as a single array and then use query to add them:

    =SPARKLINE(
  QUERY(
    {
      GOOGLEFINANCE("AMZN","price",TODAY()-365,TODAY(),"daily"),
      GOOGLEFINANCE("MSFT","price",TODAY()-365,TODAY(),"daily")
    },
    "select Col1, Col2+Col4",
    1
  )
)

    Rundown

    We first join the two arrays. To do so we use the array syntax {A,B} to join them row by row, making a 4 column array:

    ={
  GOOGLEFINANCE("AMZN","price",TODAY()-365,TODAY(),"daily"),
  GOOGLEFINANCE("MSFT","price",TODAY()-365,TODAY(),"daily")
}

    Now we can apply a query to get the values we want:

    =QUERY(
  {
    GOOGLEFINANCE("AMZN","price",TODAY()-365,TODAY(),"daily"),
    GOOGLEFINANCE("MSFT","price",TODAY()-365,TODAY(),"daily")
  },
  "select Col1, Col2+Col4",
  1
)

    The query is making 2 rows:

    1. The date column. Since it should be the same for both, no need to change anything
    2. The sum of the 2 Close columns (2 and 4)

    After that we can simply wrap everything to the SPARKLINE function, and we have our final result.

    If you don't like having the whitespace characters, you can remove them without any problem.

    Reference