Use case: counting the average number of consecutive days BTC closes below its SMA50D

[s-leroux]

Implement the necessary code and documentation to cover the following use case:

We want to know the average number of consecutive days BTC closes below its SMA50D.

[s-leroux]

A possible solution:

quote.select(
    (fc.named("SMA"), fc.sma(50), "CLOSE"),
    (fc.named("COMP"), fc.lt, "CLOSE", "SMA"),
).group_by(
    ("COMP",),
    (f.cnamed("COUNT"), ag.count, "COMP"),
).select(
    (fc.ne, fc.constant(0), "COMP"),
).group_by(
    (fc.named("AVG"), ag.avg, "COUNT"),
)

This requires:

• A group_by function with a different semantic from SQL, where rows are grouped by a consecutive sequence of values matching a condition.
• A set of aggregate functions (ag.count, ag.avg).

Interestingly, tables defined in seq v0.1.0 had a group_by method. We may look at that for inspiration.

[s-leroux]

Interestingly, tables defined in seq v0.1.0 had a group_by method. We may look at that for inspiration.

https://github.com/s-leroux/fin/blob/9781eeba2b47a5ebafcd08646c8844a05f86ca6b/fin/seq/table.py#L165-L193

[s-leroux]

Given the implementation in c3b851d0e2ba340f33a2fb96aa8cd856dc1fd4e6 a possible solution would be:

quote.select(
    "DATE",
    (fc.named("SMA"), fc.sma(50), "CLOSE"),
    (fc.named("COMP"), fc.lt, "CLOSE", "SMA"), # <--- WRONG: `select()` is not recursive. We can't reference columns created in the same statement
).group_by(
    "COMP",
    (ag.first, "DATE"),
    (ag.count, fc.named("COUNT"), "COMP"),
).where(
    (fc.ne, fc.constant(0), "COMP"),
).group_by(
    fc.constant(1),
    (ag.avg, fc.named("AVG"), "COUNT"),
)

• I am not quite sure of the last group_by clause.
• ~It is also worth noting that it is rather confusing to omit the DATE column in the select clause because it is implied, while it is mandatory in group_by.~ (fixed by a5aff7b3adcdf7cd7522d2b1a951088a53b93a6a)
• The first select() statement is wrong since we can't reference a column created in the same statement (whereas it is possible in create())

[s-leroux]

• The first select() statement is wrong since we can't reference a column created in the same statement (whereas it is possible in create())

Possible solution: Introduce the extend() predicate that works like create() but starts with a copy of the receiver's columns instead of an empty list.

The above statement might be rewritten as:

quote["CLOSE"].extend(
    (fc.named("SMA"), fc.sma(50), "CLOSE"),
    (fc.named("COMP"), fc.lt, "CLOSE", "SMA"),
).group_by(
...

[s-leroux]

Possible solution: Introduce the extend() predicate that works like create() but starts with a copy of the receiver's columns instead of an empty list.

Implemented in 03db264b744bada45af7790fc081b0c159335207.

[s-leroux]

...
).where(
    "COMP",
).group_by(
...

We also need a where() predicate to select rows.

The where predicate returns a series similar to the receiver, but containing only the rows satisfying some condition(s).

[s-leroux]

We also need a where() predicate to select rows.

Implemented in 1999c03e1b9489cccd9de462b15c6f04136b5f0d.

[s-leroux]

Full use case implemented in ae6efd4af3ee8d8250919043fc25524def3baa2a as:

    quote["Close"].extend(
        (fc.named("Sma"), fc.sma(50), "Close"),
        (fc.named("Comp"), fc.lt, "Close", "Sma"),
    ).group_by(
        "Comp",
        (ag.first, "Date"),
        (ag.first, "Comp"),
        (ag.count, fc.named("Count"), "Comp"),
    ).where(
        "Comp",
    ).group_by(
        "Comp",
        (ag.first, "Date"),
        (ag.avg, fc.named("Avg"), "Count"),
    )