Forum Discussion
I don't understand this logic with StockHistory function...
There's no logic with STOCKHISTORY. The function pulls information, that's it. The logic you describe is yours
Assuming you want to pull the last 10 days history for each stock/symbol in E4# and keep/display only the last day, one option:
in F4:
=LET( end_date, TODAY(), start_date, end_date -10, BYROW( E4#, LAMBDA(symbol, TAKE( STOCKHISTORY( symbol, start_date, end_date,, 0, 1 ), -1 ) ) ) )
There's no logic with STOCKHISTORY. The function pulls information, that's it. The logic you describe is yours
Assuming you want to pull the last 10 days history for each stock/symbol in E4# and keep/display only the last day, one option:
in F4:
=LET(
end_date, TODAY(),
start_date, end_date -10,
BYROW( E4#, LAMBDA(symbol,
TAKE( STOCKHISTORY( symbol, start_date, end_date,, 0, 1 ), -1 )
)
)
)
Thank you so much! I learned some new functions because of you!
By the way, why does your Excel looks so cool and neat, like a coding interface.
Like this:
My excel always looks terrible... like this:
I always have a headache when fixing my own excel because of this.
One addition to what mathetes said: you should investigate/learn how to use the LET function that allows - at least:
- To structure complex formulas
- To store intermediate results in variables that can be re-used. This instead of re-computing the same thing n times as you seem to do with (1.425/1000*VLOOKUP(MAX(IF....
I always have a headache when fixing my own excel because of this.
Your headaches are well earned and fully deserved. 😉
Assuming that is a real formula you've created, you are for one thing trying to put too much into one formula. Especially when that involves deep nesting of IF functions. Those become virtually impossible to maintain.
It would behoove you to learn other ways to approach that. IFS, for one. Even better: through thoughtful design of tables, which can be used as alternative ways to check on whether or not conditions are met, via all the different lookup and reference functions.
