0

Data

Each value in B is the amount that the Federal Reserve balance sheet changed for a given week.

enter image description here

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:

enter image description here

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.

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

3 Answers 3

1

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

drop

1
  • Very nice Daniele! Looks similar to the Excel formulas.
    – dharmatech
    Jun 5 at 20:27
0

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 =
                new CsvReader(new StreamReader(@"..\..\..\WALCL.csv"), config)
                .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:

enter image description here

0

I was also able to set this up in Excel.

Note the three additional columns below:

enter image description here

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.

Example file

Link to spreadsheet.

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.