You can use SPARKLINE line chart to create a stocks trendline in Google Sheets by using the formula:
=SPARKLINE(GOOGLEFINANCE("NASDAQ:GOOG","price",TODAY()-360,TODAY()),{"linewidth",2;"color","RED"})GOOGLEFINANCE("NASDAQ:GOOG","price",TODAY()-360,TODAY()) will grab the 1 year historical price data for GOOG stock.
linewidth",2; sets the thickness of line in the chart, and "color","RED" sets the color of the line.
If you just copy and paste the formula above to a cell, you will get a line chart looking like this.
We can also use conditional IF to set the color of the line to be GREEN if it is on an uptrend, and RED if it is a downtrend so that the charts look something like this.
To determine if it is an uptrend or a downtrend, we will use this simple logic. If the price of the stock 1 year ago is greater than the price today, then it is an downtrend (RED), else it will be up trend (GREEN).
Based on the above logic, we can use the following formula to determine the color of the line:
=IF(
index(GOOGLEFINANCE("NASDAQ:GOOG","price",TODAY()-360,Today()),2,2)>
index(GOOGLEFINANCE("NASDAQ:GOOG","price",TODAY()-360,Today()),ROWS(GOOGLEFINANCE("NASDAQ:GOOG","price",TODAY()-360,Today())),2),
"RED","GREEN")When you run this formula: =GOOGLEFINANCE("NASDAQ:GOOG","price",TODAY()-360,Today()) on Google Sheets, it will return a range of one year’s historical price of google.
What the INDEX(GOOGLEFINANCE("NASDAQ:GOOG","price",TODAY()-360,Today()),2,2) does is return the value of the range that is in the 2nd row and 2nd Col (2,2). In this case it will be the price of the stock a year ago.
Next we will need to know the price of the stock at the most recent date in the range of historical data. So we need to know the max number of rows in this range.
To get the max number of rows we just need to use the ROWS function: ROWS(GOOGLEFINANCE("NASDAQ:GOOG","price",TODAY()-360,Today()))
INDEX(GOOGLEFINANCE("NASDAQ:GOOG","price",TODAY()-360,Today()),ROWS(GOOGLEFINANCE("NASDAQ:GOOG","price",TODAY()-360,Today())),2) will return the most recent price.
The "RED","GREEN" means if logic is true, return "RED", if false then return "GREEN".
Once you are able to determine if it’s red or green, you can then reference the color to the formula, and you will be able to color code your stocks trendline using SPARKLINE in Google Sheets.
