Forum Discussion

AlphaEra's avatar
AlphaEra
Copper Contributor
Dec 18, 2024
Solved

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 t...
excel
Formulas and Functions
office 365
  • Lorenzo's avatar
    Lorenzo
    Dec 20, 2024

    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 )
    )
  )
)

     

AlphaEra's avatar
AlphaEra
Copper Contributor
Dec 20, 2024

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.

Lorenzo's avatar
Lorenzo
Silver Contributor
Dec 20, 2024

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....

Resources