LAMBDA Function to create a dynamic array from data whose rows grow (or shrink) - _FredRange

[jdellasala]

A while back I discovered an Excel Add-In called FRED Data provided by the Federal Reserve Bank of St. Louis. What it does is allow you to retrieve data from various Federal agencies such as the Consumer Price Index from the U.S. Bureau of Labor Statistics.

The problem is that the data is retrieved as plain values of Date and Value (just 2 columns) making formulas difficult. The data can’t be put into a Table because when the data is updated or the frequency (Annual, Quarterly, etc.) or as new rows are added for newer reports changes, the Table does not.

I created a function I call _FredRange whose only parameter is the cell of the top left cell of the data (first Date here) and produces a two column array (date and value as provided by the FRED add-in). The formula is:

=LAMBDA(fCell,IF(ISFORMULA(fCell),"Formula in "&ADDRESS(ROW(fCell),COLUMN(fCell),4)&": "&FORMULATEXT(fCell),""))

The function dynamically creates an array of the data by counting the number of rows in the data provided by FRED two columns wide. The optional Cols parameter allows for including an extra column that adjusts the data provided. For example, the Unemployment Rate is provided as a Percent value, but the value is actually the Percent value times 100. To get the actual percentage the values need to be divided by 100 which could be done in a 3rd column next to the data FRED provides and included in the dynamic array created by _FredRange.

The FILTER at the end is because sometimes a date is provided in the first column, but the Value provided is #N/A – meaning the most recent value which hasn’t been provided yet.
What I’ve found is creating Named Ranges for the resulting array allows for easily using the data.
For example, if I use the formula in Cell E8 for CPI (Consumer Price Index) data, I can easily create 3 Named Ranges to utilize the data. For example the range Cpi which is =F8# to reference all the data retrieved by _FredRange, CpiDt =INDEX(Cpi,,1) to reference the Date column, and CpiVal =INDEX(Cpi,,2) to reference the Value column.

I wouldn't be surprised if a similar LAMBDA function has already been posted - with possibly probably has better code, although acquiring this kind of data that cannot be easily manipulated using tables or formulas, or Power Query is unusual. If nothing else, the FRED add-in is certainly worth the read!

P.S. I start all of my LAMBDA functions start with an underscore to make them easier to find.

 

[jasonb75]

I think you've done one of my tricks and put the wrong formula in the post

 

[jdellasala]

Jeez! And on my first post!! The correct formula is:

=LAMBDA(TopLeft,[Cols],LET(Aary,OFFSET(TopLeft,,,COUNTA(INDIRECT(ADDRESS(ROW(TopLeft),COLUMN(TopLeft),1)&":"&ADDRESS(1048576-ROW(TopLeft),COLUMN(TopLeft),1))),IF(ISOMITTED(Cols),2,Cols)),FILTER(Aary,ISNUMBER(INDEX(Aary,,2)))))

Thanks for the heads up! Wish I could just fix the original post not just to prevent confusion, but to stop the embarrassment!