# Historical stock price "changepct"

`=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?

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

``````=sparkline(query({
},
"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"})
``````
• 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, 2017 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, 2017 at 6:48