Help request: Daily net worth using balance history

Microsoft Excel
1

I am trying to use the balance history tab to create a dataset that would ultimately give me a simple line chart of net worth over time with a point for each day and where I could zoom to specific date ranges. I know there is a sheet that does this (and is great) but I’ve started down the rabbit hole of trying to make one myself and was hoping for a clue.

I think what I want is a data set that has one row per date per account with one column for balance (positive for asset, negative for liability). Challenge is that some of my accounts are manual and do not change often, for those I would just want the last recorded balance. Also some accounts update more than once per day in the history tab, so for those I’d just want the last balance of the day.

I just don’t know what phrase to google to get me started on this. What I’ve tried so far is a pivot table with “Add to data set” and a date table in power pivot related to the Date field in Balance History but I’m not sure what else to do in the pivot table to overcome the rows that don’t exist for every day.

Thanks for any help. I’m on day 3 of using this and loving it so far. Long time mint user looking for my replacement.

1 Like
2

My first thoughts for accomplishing your goals would be to create a new table that has dates on the left and a column for each account. For each cell you could then have an if statement that says, if there is data for that account on that day in the balance history table then populate from balance history, else populate from the previous day of the table.

However, I was just playing around with this and I think I found an option with the pivot table you are looking for. Well not the pivot table but either a pivot chart or a regular chart. Once you have the data in a chart you can right click the chart and choose ‘select data’. In the bottom left corner of the new window click the box that says ‘Hidden and Empty Cells’. There you will find an option that says ‘Show empty cells as:’ and I believe the option you want is ‘Connect data points with line’.

Here is the table with the default setting of ‘Gaps’:

image
image1304×412 20.2 KB

Here is the table with the setting changed to ‘Connect data points with line’.

image
image1310×413 34.8 KB

I still think the first option with a new table will give you more flexibility but the choice is obviously yours.

1 Like
3

Glad to hear you’re liking Tiller, @ajhops.

This is a little bit of a fussy problem. The Net Worth template is worth a look. It has some of the logic you are asking for— trying to find a balance in a monthly range then using the most recent balance before that if one is not found in the period. This query could be manipulated to work for you. The trouble is that it is a pretty processor intensive query if there are a lot of account and a lot of days… so proceed with caution.

1 Like
4

Thank you both for your help. Reverse engineering the Net Worth template is helpful too. I think I might look into creating a macro to populate my source table on demand. Appreciate the input!

1 Like
5

:wave:, @ajhops

Did any of these suggestions help? If so, please mark one as the solution.

mark-solution
mark-solution1640×176 33 KB

6

I ended up writing a macro that does it. The pertinent part of the code is pasted below.

Function Get_Recent_Balance(ByVal balanceDate As String, ByVal account As String) As Double
Debug.Print ("=INDEX(SORT(FILTER(BalanceHistory,(BalanceHistory[Account]=""" & account & """)*(BalanceHistory[Date]<=DATEVALUE(""" & balanceDate & """)+1),0),1,-1),1,8)")

Dim balance As Double: balance = Evaluate("=INDEX(SORT(FILTER(BalanceHistory,(BalanceHistory[Account]=""" & account & """)*(BalanceHistory[Date]<=DATEVALUE(""" & balanceDate & """)+1),0),1,-1),1,8)")
Get_Recent_Balance = balance

End Function