Time series data

1

I’m working on a financial modeling application and was wondering if anyone has tried to work with time series data in Coda, such as cash flows that are spread across a number of months?

2

Did you make any progress here Todd? I’m also interested in this

3

Somewhere in here there is a really brillant time series doc that you guys should see. I will try to find it

4

Ah… Todd it was your doc. But @Brandon_Trew this should help.

5

Thank you, but that link isn’t working any longer. Can you try update the link, or share the published version?

In the mean time I was playing around, and came up with this as a simple starting point: https://coda.io/@brandon-trew/template-time-series-scenario-modeling

6

Thank you @Connor_McCormick and @Brandon_Trew for your interest. I have now published the Time & Money prototype at https://coda.io/@cic-service/time-money-prototype.

7

Hi @Todd_Olson,

There seems to be some formatting strangeness in the Rules section:

image
image1624×1288 97.9 KB

8

Thanks Todd,

This is outstanding. You’ve really inspired me with this (as a LONG time excel modeling geek - here’s my published excel course). Without exaggerating, I think this could be the killer app that helps improve on excel (for all the reasons you mentioned)

You’ve blown my mind with:

  • REALLY smart with the separation of rules.
  • And really interesting separation of the schedules logic (define the discreet time periods upfront). Really novel use of these two structural components.
  • The use of “top grouping” to create the output “pivot table” is genius. I totally missed that this was possible. I’m in love.

I think the combination of our two docs (here’s mine) would make it a complete package:

  • My approach to scenario paramaterization would meaningfully improve your template I feel, given how scalable it it to add scenarios. It’s also really easy to use them in formula.
  • I also found a way to fully automate creation of the time periods, using a little hack of both buttons and formulamap. This would let you autogenerate your entire cashflow table dynamically. There’s also a button to clear the whole table, so that it’s easy to keep “fresh”. Thinking ahead - would be amazing to add an “actuals” table separately. Then to have a rule that pulls actuals into the core cashflow model when available (so that doing BvAs becomes a cinch)

Quick FYI: you need a line break after the intro text in the Rules table (see image):

Screen Shot 2020-05-08 at 11.23.53 AM
Screen Shot 2020-05-08 at 11.23.53 AM2646×990 255 KB

1 Like
9

Formatting should be okay now. Coda created some formatting options since I first created this prototype that I needed to respond to.

10

@Brandon_Trew, thank you so much for your kind comments. I will look at your doc; it sounds like an exciting possibility for improving on what I did. I haven’t worked on Time & Money in a while, but you, along with some other commenters, are getting the juices flowing again. I need to decide if/how/with what resources to pursue this further.

11

I really like this. I love how I can change the content of the initial data column interactively, even after hitting the model button, and it updates dynamically.

In particular, this gem (in column BTN_Create_Time) is super useful:

FormulaMap(
      Sequence(1, thisRow.End_Date-thisRow.Start_Date),
      AddRow([Time series Model], 
            [Time series Model].Scenario, thisRow,
            [Time series Model].Date,
                       thisRow.Start_Date+CurrentValue)
)

It’s so useful and probably common it should almost be considered a candidate for being its own formula. Perhaps something with this signature, AddRows(TargetTable, Iterable, Column 1, Value 1, Column 2, Value 2...) so you would have AddRows(TableName, Sequence(1, End Date - Start Date), Scenario, thisRow, Date, Start Date + CurrentValue)

Couple questions:

  1. Why not use a multiselector for your Scenario matches selector?
  2. Why not disable the buttons in BTN_Create_Time when the Time series Model table already contains that row’s scenario? (Could be disabled with [Time series Model].Scenario.Unique().Contains(thisRow))
  3. Finally, and somewhat strangely, the Time Series Chart comes out to show 10PM with your rows, but with mine it ends on the exact date. Fixed this by reselecting the end date. Bug worth keeping in mind.
    image
    image414×864 19.1 KB
12

Thanks for the feedback!

  1. What do you mean by multi-sector for scenario selector? Do you mean that the variables could change (by time or something) even within a scenario?

  2. The main reason you need to hit the button, is when you change the time ranges for a scenario. If you just change parameters, then the model updates by itself. So you’d need to run a check whether the min date and max date were the same as in the scenario.

  3. That bug is strange. I can’t get my chart labels to fix. What do you mean by “Fixed this by reselecting the end date.”?

13

  1. Multiselector just lets you select multiple options at the same time. It’s nice if you want to compare just a subset of the Scenarios. Found under the selector settings:

    image
    image720×184 3.51 KB

  2. I noticed that if I hit the button multiple times it seemed to create a new scenario each time. Maybe it should first delete the existing matching scenario before creating the new one? Would also be cool if it only became available to press if that exact scenario wasn’t already created, then I would know that I should press the button again whenever it turned pink.

  3. I mean I just clicked the same end date that was already there, and then clicked the Model! button. I’m guessing it started as a datetime type column, and has subsequently been turned to only a date column.

    image
    image424×628 29.8 KB

14

  1. Dope. I misread as multi-SECTOR". multi-selector makes a lot more sense, yes! I actually really dislike the usability of the multi-selector component. It’s super clunk to use I find when most of the useful interactions come from “filter one”, or “all”. For the chart, you’re right that multi-select would be better. I really wish Coda team would redesign it like Google Sheets does 0 with a list of checkboxes, and an “all / none” hotlink above". This list, with the “x’s” is really clumsy.

  2. Good points. I’ve tweaked the logic, to disable the row buttons if the table is populated. When in practice, I planned to hide the row buttons entirely, and have the model run only from the “run model” button. Much easier to reset the whole table once in a while, than try hand tune the individual scenarios. I’m sure when the models get really large, this will become too cumbersome and will need a more surgical approach.

  3. Got word from support - there’s a bug in which the chart doesn’t respect the table column formatting. As you say - this started because I had changed the time inputs while it was still a date+time column, before changing to date only. I’ve deleted the original values and rerun it, so it’s now working.

Thanks for the quick-fire feedback.

I’m working on a new version that can handle more extensive model logic (building on Todd’s time and money framework, which I love). Will share with you once I’m a little further. Keep it coming!

15
  1. Ah that’s funny, I misread your sector as selector. I was thinking to myself, “This guys sure does seem to have a good grasp of Coda to not have encountered multi-selector.” But that makes sense now.
  2. Yeah I think I agree with your choice to only “Run Model”, though for really slow calculations, or multiple scenarios there may be an argument on behalf of the “Update Scenario” option. Might be nice to use conditional formatting to indicate to the user when they would need to update the scenario (i.e. they changed a date) vs it auto updates (i.e. changed only the Initial Value)
  3. Nice!

Very excited to see it! Nice work!