I can currently get the 24hr percent change
of a stock in Google Sheets by:
=GOOGLEFINANCE("NASDAQ:GOOGL","changepct")
How do I get the 1-hr
, 7-day
and 1-month
percent changes of a stock(or even a custom amount)?
CodePudding user response:
if by 1-hr
you mean change between this hour and previous hour then 1-hr
percent change is not possible to get by formula. could be doable via script where you would run a trigger every hour to log the values and then just compute the change with a simple formula.
changepct
stands for "percentage change in price since the previous trading day's close" so to be precise it is not the 24-hr
unless you look at it at 16:00:00
when the closing happens. if you wish for a true 24-hr
then again, you need to resolve it with a script that will log the values on an hourly basis and compute the change with a simple time-moving formula
7-day
and 1-month
are possible.
to calculate percentage change the formula is:
translated into a formula it would be:
=((GOOGLEFINANCE("NASDAQ:GOOGL", "price")-INDEX(
GOOGLEFINANCE("NASDAQ:GOOGL", "price", TODAY()-6, TODAY()), 2, 2))/INDEX(
GOOGLEFINANCE("NASDAQ:GOOGL", "price", TODAY()-6, TODAY()), 2, 2))*100
=((GOOGLEFINANCE("NASDAQ:GOOGL", "price")-INDEX(
GOOGLEFINANCE("NASDAQ:GOOGL", "price", TODAY()-29, TODAY()), 2, 2))/INDEX(
GOOGLEFINANCE("NASDAQ:GOOGL", "price", TODAY()-29, TODAY()), 2, 2))*100