Forum Discussion
I don't understand this logic with StockHistory function...
I don't understand this....
I will show my question step by step:
1. =STOCKHISTORY(E4,TODAY()-10,TODAY(),,0,1) Gives me an array.
2. I use ROWS and INDEX to get the last number of the array.
=INDEX(STOCKHISTORY(E4,TODAY()-10,TODAY(),,0,1),ROWS(STOCKHISTORY(E4,TODAY()-10,TODAY(),,0,1)))
gives me the last number of the array.
3. I change "E4" to "E4#" =INDEX(STOCKHISTORY(E4#,TODAY()-10,TODAY(),,0,1),ROWS(STOCKHISTORY(E4#,TODAY()-10,TODAY(),,0,1)))
Gives me the 0.67....which is the price of "8zz" the last one on E4#.
I was expecting to get the last number of each and every StockHistory array of every elements in E4#... Instead, I only get the "first" number of the last element in E4#.
I don't understand the logic of this function....Can anybody explain this strange behavior?
Thank you very much!
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 ) ) ) )
6 Replies
You are so awesome!
Thank you very much!
I learned a lot of functions because of you!Thanks a lot!
You're welcome & thanks for providing feedback
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....
