Using the Federal Reserve Bank of St. Louis' Add-In, I am able to download historical rate data for multiple different securities (10-Year Treasury, 3-Month LIBOR, etc.) This is incredibly useful because I can update this data with by simply running the macro and it gives me all the historical data alongside the newest information.
The information looks like this after I run the Macro:
<tbody>
</tbody>
The data extends all the way down until the date with the latest information.
On a different tab, I need to be able to lookup what the maximum date in each range is and corresponding value next to it by identifying date column/corresponding value column via the codes present in A1 (DGS3MO) and A3 (DGS1). Of course, it has to adapt to when I update the macro and new dates and values are added. In addition, I have a bunch more rates than what I have shown in the example, so it would be great if the formulas wouldn't be restricted to just these four columns, but also included the ability to adapt to adding new columns as well.
I've tried a variety of different vlookup, hlookup, and index/match combinations but just haven't been able to get it to work. Any help would be GREATLY appreciated. Thank you so much!
The information looks like this after I run the Macro:
| A | B | C | D | |
| 1 | DGS3MO | DGS1 | ||
| 2 | lin | Percent | lin | Percent |
| 3 | D | Daily | D | Daily |
| 4 | 01/01/1900 | 1982-01-04 to 2015-02-18 | 01/01/1900 | 1962-01-02 to 2015-02-18 |
| 5 | 3-Month Treasury | 1-Year Treasury | ||
| 6 | Board of Governors | Board of Governors | ||
| 7 | Date | Value | Date | Value |
| 8 | 01/04/1982 | 11.9 | 01/02/1962 | 4.1 |
| 9 | 01/05/1982 | 12.2 | 01/03/1962 | 4.0 |
| 10 | 01/06/1982 | 12.2 | 01/04/1962 | 4.0 |
| 11 | 01/07/1982 | 12.2 | 01/05/1962 | 4.0 |
| 12 | 01/08/1982 | 12.0 | 01/06/1962 | 5.0 |
| 13 | 01/09/1982 | 12.3 | 01/07/1962 | 4.0 |
| 14 | etc... | etc... | etc... | etc... |
<tbody>
</tbody>
The data extends all the way down until the date with the latest information.
On a different tab, I need to be able to lookup what the maximum date in each range is and corresponding value next to it by identifying date column/corresponding value column via the codes present in A1 (DGS3MO) and A3 (DGS1). Of course, it has to adapt to when I update the macro and new dates and values are added. In addition, I have a bunch more rates than what I have shown in the example, so it would be great if the formulas wouldn't be restricted to just these four columns, but also included the ability to adapt to adding new columns as well.
I've tried a variety of different vlookup, hlookup, and index/match combinations but just haven't been able to get it to work. Any help would be GREATLY appreciated. Thank you so much!
