=LET(
target, ONDAY(),
startDate, IFERROR(WORKDAY(target,-305,holidays),""),
rawAll, IFERROR(GOOGLEFINANCE(TICKER(),"all",startDate,target,"DAILY"),),
dataAllRaw, IFERROR(QUERY(rawAll,"select * offset 1",0),),
dataAll, IFERROR(ARRAYFORMULA(HSTACK(TRUNC(INDEX(dataAllRaw,0,1)), INDEX(dataAllRaw,0,2), INDEX(dataAllRaw,0,3), INDEX(dataAllRaw,0,4), INDEX(dataAllRaw,0,5), INDEX(dataAllRaw,0,6))),),
todayRow,
IF(
target=TODAY(),
IFERROR({
target,
IFERROR(GOOGLEFINANCE(TICKER(),"priceopen"),
INDEX(GOOGLEFINANCE(TICKER(),"open",target,target,"DAILY"),2,2)),
IFERROR(GOOGLEFINANCE(TICKER(),"high"),
INDEX(GOOGLEFINANCE(TICKER(),"high",target,target,"DAILY"),2,2)),
IFERROR(GOOGLEFINANCE(TICKER(),"low"),
INDEX(GOOGLEFINANCE(TICKER(),"low",target,target,"DAILY"),2,2)),
IFERROR(GOOGLEFINANCE(TICKER(),"price"),
INDEX(GOOGLEFINANCE(TICKER(),"close",target,target,"DAILY"),2,2)),
IFERROR(GOOGLEFINANCE(TICKER(),"volume"),
INDEX(GOOGLEFINANCE(TICKER(),"volume",target,target,"DAILY"),2,2))
})
),
combinedRaw, IF(target=TODAY(), VSTACK(todayRow, dataAll), dataAll),
cleanedRows, IFERROR(QUERY(combinedRaw,"select * where Col1 is not null",0),),
uniqueDates, IFERROR(UNIQUE(INDEX(cleanedRows,0,1)),),
dedupRows,
IF(
COUNTA(uniqueDates)=0,
cleanedRows,
MAP(
uniqueDates,
LAMBDA(d,
INDEX(
FILTER(cleanedRows, INT(INDEX(cleanedRows,0,1)) = INT(d)),
1, 0
)
)
)
),
sortedRows, IFERROR(SORT(dedupRows,1,TRUE),),
rowCount, ROWS(sortedRows),
trimmedRows,
IF(rowCount>=301,
QUERY(sortedRows,"select * offset " & (rowCount-301),0)
),
padRowsBlock,
IF(
rowCount<301,
LET(
padCount, 301 - rowCount,
firstDate, INDEX(sortedRows,1,1),
baseOpen, IFERROR(INDEX(FILTER(sortedRows, INDEX(sortedRows,0,2)<>""),1,2),0),
baseClose, IFERROR(INDEX(FILTER(sortedRows, INDEX(sortedRows,0,5)<>""),1,5), baseOpen),
baseVol, IFERROR(INDEX(FILTER(sortedRows, INDEX(sortedRows,0,6)<>""),1,6),0),
padDates, SEQUENCE(padCount,1, firstDate - padCount, 1),
padOpen, SEQUENCE(padCount,1, baseOpen, 0),
padClose, SEQUENCE(padCount,1, baseClose, 0),
padHigh, padClose * 1.01,
padLow, padClose * 0.99,
padVol, SEQUENCE(padCount,1, baseVol * 0.05, 0),
padBlock, HSTACK(padDates, padOpen, padHigh, padLow, padClose, padVol),
VSTACK(padBlock, sortedRows)
)
),
fullRaw, IF(rowCount>=301, trimmedRows, padRowsBlock),
processed,
MAP(
INDEX(fullRaw,0,1),
INDEX(fullRaw,0,2),
INDEX(fullRaw,0,3),
INDEX(fullRaw,0,4),
INDEX(fullRaw,0,5),
INDEX(fullRaw,0,6),
LAMBDA(d, o, h, l, c, v,
LET(
oVal, IF(ISNUMBER(o), o, ""),
cVal, IF(ISNUMBER(c), c, ""),
hVal, IF(ISNUMBER(h), h, IF(AND(oVal<>"", cVal<>""), MAX(oVal,cVal)*1.01, IF(oVal<>"", oVal*1.01, ""))),
lVal, IF(ISNUMBER(l), l, IF(AND(oVal<>"", cVal<>""), MIN(oVal,cVal)*0.99, IF(oVal<>"", oVal*0.99, ""))),
closeOut, IF(cVal<>"", cVal, IF(oVal<>"", oVal*0.995, "")),
volOut, IF(ISNUMBER(v), v, 0),
HSTACK(d, IF(oVal<>"", oVal, ""), hVal, lVal, IF(closeOut<>"", closeOut, ""), volOut)
)
)
),
readyFlag, IF( OR( NOT(ISNUMBER(INDEX(sortedRows,1,2))), NOT(ISNUMBER(INDEX(sortedRows,1,6))) ), 0, 1),
IF(readyFlag=0, "Loading...", processed)
)