Google-sheets – Google Spreadsheet Stock Sparkline Trend Conditional Formatting

google sheetsgoogle-finance

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

Best Answer

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")})
Related Topic