How to create a cash flow chart that shows where I am at today in terms of spending vs last month on the same day

I know that this is not the google sheets support forum, but hopefully someone here can help me with a rather specific request, and if not I will go back to the google sheets support forum.

I use to use personal capital but since I moved to Tiller haven’t looked back at PC. However, I really like this graph they give me (if it were accurate).

This graph shows the x axis that is the day of the month and is a cumulative cash flow as of today vs last month on the same day. I like this and I can come up with the data really simple using the query formula to create a summary of each day both for the current month and for the last month then use an arrayformula + vlookup to map this into each day since there will be days where perhaps I didn’t spend anything yet you still want to count those days since then you will be creating a running total.

However, what i need help with is how to create the dynamic x axis for the day of the current month. I thought of a brute force way of doing this by having a seaprate sheet with a column for each month showing the number of days and then i can use a formula to pick the right column to use but wondering if there is a simpler way?

I want to have this graph bc to me i use it as a quick glance into how am I doing vs last month on the same time also there is some seasonality that happens with regards to spending where in the beginning you have more spending due to bills then it tapers off.

2 Likes

That’s a cool idea, @mar5.
I was intrigued and took a stab at building the report as you describe it.

Have a look at it here:

And here is a screenshot:

Let me know what you think.
Randy

P.S. If the accounts and payees look a little weird, I ran my sheet through the Tiller Labs Spreadsheet Sanitizer to remove personal data. :wink:

2 Likes

How did you come up with the number of days dynamically or you just feed them manually? I wanted to make that column be dynamic to the current month.

Also never used sumproduct(). May need to research how to use this formula and how it fits in my other ways of doing this to see if it is more efficient. Could you also use this formula with an array formula statement enclosing it all this way it can dynamically go down? Not sure you can since it is already taking a matrix already.

I added a sheet called Compare To Last Month - Dynamic with a small change that adapts the days column to line up with today’s date.

SUMPRODUCT() is pretty cool. Worth learning about.

Randy

2 Likes

Intuit’s online Mint program, which is free, has exactly the chart you are looking for on their Overview page. They also have lots of great budgeting stuff. Check it out.

1 Like

Thanks! I’m very familiar with Mint but I prefer Tiller and have tried various budgeting tools but I prefer classic spreadsheets and look to create a single pane of glass dashboard that incorporates like features from these other budgeting platforms.

2 Likes

Here’s the chart I have on my dashboard which does what you want. Obviously, the data is on a separate sheet. It can be adapted to any interval…days, weeks, months, etc.

1 Like

I’ve revised the chart I sent to you a couple of days ago to more resemble what you’re looking for.

1 Like

:wave:, @basprint! Glad to see you here :slight_smile:

Maybe consider sharing your workflow, how you built this, and an example sheet as part of our 2019 Builder’s Challenge?

I’ve always enjoyed seeing the stuff you’ve built and shared with me via support! Keep up the great work :wink:

Heather

This is fantastic. I second what @heather said - enter this in the Spreadsheet Builders Challenge!