yogi_Compute True Range (max of Hig-Low abs(High-Closeyest) abs(Low-Closeyest)) for a Ticker Symbol

Google Spreadsheet   Post  #2197

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-03-2017

question by Tommy Schaefer:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/gENldwEI040;context-place=forum/docs

Calculating Average True Range with Google Finance

I was wondering if anyone figured out how to calculate Average True Range (ATR) using Google's finance function. I have figured out (I believe) how to calculate the True Range with this:

True Range =max((GoogleFinance(ticker,"high")-GoogleFinance(ticker,"low")), ABS(GoogleFinance($A2,"high"))-GoogleFinance(ticker,"closeyest"), ABS(GoogleFinance(ticker,"low"))-GoogleFinance(ticker,"closeyest"))

However, I'm drawing a blank on how to do the same calculation for the previous 13 days. Essentially I want to know the ATR for a ticker over 14 days without having to store the True Range for all 14 days. I'd rather the cell just do all the calculations and displayed the ATR. Can anyone help??

Thanks!