4

=sparkline(query(GOOGLEFINANCE(A20,"price",today()-30,TODAY(),"DAILY"),"select Col2",-1),{"charttype","column";"highcolor","red"})

The function above provides a visual representation of daily price movement for the last 30 days in a specific cell. Is it possible to similarly track only the daily 'changepct" for the last 30 days?

4

Here's the first try at this (with linebreaks for readability):

=sparkline(query({
    googlefinance("goog", "price", today()-30, today(), "daily"),
    googlefinance("goog", "price", today()-31, today()-1, "daily")
  }, 
  "select Col4/Col2 - 1", 1), 
{"charttype", "column"; "highcolor", "red"})

This calls googlefinance twice for two ranges, the second being offset by one day. Then Col4/Col2 - 1 is the change relative to previous day. If multiplied by 100, it would be the percentage change but seeing as sparkline does not have a scale anyway, I didn't bother to.

However, the above formula may run into trouble because of weekends and holidays, as the price quotes are given for business days only. The two date ranges, although equal in the number of calendar days, may differ in the number of business days. This can be avoided by using array_constrain to limit the number of rows to, say, 20:

=sparkline(query({
    array_constrain(googlefinance("goog", "price", today()-30, today(), "daily"), 20, 2),
    array_constrain(googlefinance("goog", "price", today()-31, today()-1, "daily"), 20, 2)
  }, 
  "select Col4/Col2 - 1", 1), 
{"charttype", "column"; "highcolor", "red"})

If the number of googlefinance calls becomes an issue (that is, you have many such sparklines, and doubling the number of calls exceeds the quota), a workaround is to put each googlefinance separately somewhere else, and then refer to that output liks so:

=sparkline(arrayformula(N1:N24/N2:N25 -1),{"charttype","column";"highcolor","red"})
2
  • Thanks Michelle. While your first suggestion worked beautifully for US stocks, it showed the below error when I started to use Indian stock data : Error Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 21. Actual: 22. I managed to get around this by modifying the formula slightly to googlefinance("goog", "price", today()-31, today()-2, "daily")
    – Leo
    Aug 19 '17 at 5:10
  • The number of business days isn't the same in both periods, I suppose. I expanded the answer to address this point.
    – user135384
    Aug 19 '17 at 6:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.