I am trying to do a Sparkline whose color changes based on the trend and for some reason the if clause which gets its input from google finance isn't working

=sparkline(query(googlefinance($C5, "price", today()-365, today()-2), 
 "select Col2 label Col2 ''", 1),{"color",
 if((googlefinance($C5,"price",today()-365)) < 
 (googlefinance($C5, "price", today()-2)),"green","red")})

What is that I am doing wrong $C5 you can put any ticker, its always giving me red


1 Answer 1


Historical data will always give a 2x2 array with headers,date and price. Use INDEX to get only the price.

=sparkline(query(googlefinance($C5, "price", today()-365, today()-2), "select Col2 label Col2 ''", 1),{"color",if( INDEX(googlefinance($C5,"price",today()-365),2,2) < INDEX(googlefinance($C5, "price", today()-2),2,2),"green","red")})

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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