Forum Discussion

dandan_dain's avatar
dandan_dain
Copper Contributor
Oct 30, 2024

STOCKHISTORY skipping days in a macro

I have an excel file that uses the STOCKHISTORY function for FOREX rates needed on a report. However some tabs are #N/A since some of the days are missing

 

For example, it would start on September 2 (09/02/2024) and not on September 1 (09/01/2024)
Anyone knows a solution pls?

 

Excel
Formulas and Functions
Macros and VBA
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Oct 31, 2024

    dandan_dain 

    STOCKHISTORY() skips non trading dates. If with formula it could be

    =LET(
  start, DATE(2024,8,1),
  end,   TODAY(),
  dates, SEQUENCE( end-start+1,,start),
  pair,  "GBP/USD",
  stock, STOCKHISTORY(pair, start, end,0,0,0,1),
  rates, XLOOKUP(dates,CHOOSECOLS(stock,1),CHOOSECOLS(stock,2),,-1),
  HSTACK(dates, rates)
)

Resources