# Weeks since larger drop

## Data

Each value in `B` is the amount that the Federal Reserve balance sheet changed for a given week. ## Largest drop since...

This past week, the balance sheet decreased by \$31,617.

This is the largest drop since Dec 29th.

These values are highlighted in the data above.

## Question

It would be nice to have a column that calculates this for us. I.e. something like weeks since larger drop.

Example with the last 4 manually filled out: So, on `2022-05-25` when the balance sheet dropped `\$31,617`, it was the biggest drop since `2021-12-29`. And it had been `21` weeks since that larger drop.

What's a good way to have a formula that calculates these extra columns?

## Logic

Seems like the logic should be:

• Search backward for the lower value
• Get the date at that row
• Compute date diff. Find number of weeks from that.

## Link to sheet

Here's a link to the Google Sheet shown above, in case you'd like to experiment on the data.

• What qualifies as a large drop? From what I can see, row 132 is a pretty large drop, comparatively larger than row 140 anyways. May 29 at 6:01
• I understood that it is necessary to take into consideration the FIRST drop larger than the current one among the previous ones May 29 at 6:28
• @Daniele, For now I've used a C# program to generate the additional columns. It's provided below. Jun 2 at 8:37
• @Daniele, I've also set this up in Excel: webapps.stackexchange.com/a/164945/293007 Jun 4 at 4:31

This formula for "Last Larger Date" works on google sheet (it must be dragged) and gives a result that matches the image of your first post:

``````=INDEX(IFNA(IF(
\$B2="","",
IF(\$B2 > 0,
LOOKUP(2, 1 / (N(\$B1:B\$2) > \$B2), IF(\$B1:B\$2<0,\$A1:A\$2,)),
LOOKUP(2, 1 / (N(\$B1:B\$2) < \$B2), \$A1:A\$2)
))))
``````

for the week count this calculation is enough (no need to drag it):

``````=INDEX(IF(C2:C<>"",DIVIDE(A2:A-C2:C,7),))
`````` • Very nice Daniele! Looks similar to the Excel formulas. Jun 5 at 20:27

For now, I've used a C# program to import the CSV data and output a new CSV file with the additional columns.

Program:

``````using System;
using System.Globalization;
using CsvHelper;
using CsvHelper.Configuration;

namespace WalclWeeksSinceLarger
{
public class FedBalanceSheetCsvRow
{
[CsvHelper.Configuration.Attributes.Index(0)] public DateTime Date { get; set; }
[CsvHelper.Configuration.Attributes.Index(1)] public decimal Change { get; set; }
}

public class Data
{
public DateTime Date { get; set; }
public decimal Change { get; set; }
public DateTime LastLarger { get; set; }
public int WeeksSinceLarger { get; set; }
}

internal class Program
{
static void Main(string[] args)
{
var config = new CsvConfiguration(CultureInfo.InvariantCulture) { };

// From:
//
//     https://fred.stlouisfed.org/graph/?g=PRKH

var fed_balance_sheet_items =
.GetRecords<FedBalanceSheetCsvRow>()
.ToList();

var result = fed_balance_sheet_items.Select(item =>
{
var ls = fed_balance_sheet_items.TakeWhile(elt => elt.Date < item.Date).Reverse().ToList();

var null_data = new Data()
{
Date = item.Date,
Change = item.Change
};

if (item.Change == 0) return null_data;

var obj = item.Change > 0 ?
ls.FirstOrDefault(elt => elt.Change > item.Change) :
ls.FirstOrDefault(elt => elt.Change < item.Change);

if (obj == null) return null_data;

return new Data()
{
Date = item.Date,
Change = item.Change,
LastLarger = obj.Date,
WeeksSinceLarger = (int)(item.Date - obj.Date).TotalDays / 7
};
});

using (var writer = new StreamWriter(@"..\..\..\out.csv"))
using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
csv.WriteRecords(result);

}
}
}
``````

Example Output: I was also able to set this up in Excel.

Note the three additional columns below: The data is in an Excel table (note the structured references).

# Formulas

LAST LARGER

``````= IF( [@[WALCL_CHG]] > 0,
LOOKUP(2, 1 / (\$B\$2:B1015 > [@[WALCL_CHG]]), \$B\$2:B1015),
LOOKUP(2, 1 / (\$B\$2:B1015 < [@[WALCL_CHG]]), \$B\$2:B1015))
``````

DATE OF LAST LARGER

``````= IF( [@[WALCL_CHG]] > 0,
LOOKUP(2, 1 / (\$B\$2:B1015 > [@[WALCL_CHG]]), \$A\$2:A1015),
LOOKUP(2, 1 / (\$B\$2:B1015 < [@[WALCL_CHG]]), \$A\$2:A1015))
``````

WEEKS SINCE LARGER

``````= IF( [@[WALCL_CHG]] > 0,
DATEDIF(LOOKUP(2, 1 / (\$B\$2:B1015 > [@[WALCL_CHG]]), \$A\$2:A1015), [@DATE], "d") / 7,
DATEDIF(LOOKUP(2, 1 / (\$B\$2:B1015 < [@[WALCL_CHG]]), \$A\$2:A1015), [@DATE], "d") / 7)
``````

# Notes

The above formulas do not appear to work in Google Sheets.