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.

2 Likes

: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

1 Like

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

1 Like

Hey @basprint! I actually just did this graft in google data studio as it was a faster way for me to have a dashboard. Thank you for your contribution here! I did want to do this in sheets, but since I have gone down this path with data studio so far going to see how this works.

2 Likes

How can I create this chart and place it on the dashboard? It looks fantastic.

Hi @kttns12441,

You would have to use Google’s separate service, Google Data Studio. I’m not certain you can add it to your Tiller sheet outside of Google Data Studio. I just re-read this post last week and went to try out Google Data Studio. Its cool for pulling in data and creating reports, but it also has its own learning curve.

If you are comfortable, you can try using the built-in Pivot Table options to add charts. This is what I’m going to try to see if I can recreate it inside the Tiller sheet. No promises on WHEN I would figure this out, however, once I do I will be happy to post an update here.

Hi

I am sorry I didn’t reply to @kttns12441 because I thought he was referring to the other graph.

I posted a template for anyone to get started with Google Data Studio here

Hope it helps!

Thanks

Miguel

1 Like

@mar5 Thanks. I’ll go take a look.