Tracking Your Net Worth (Excel Experts needed)

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills.
Post Reply
Topic Author
Dufus
Posts: 181
Joined: Mon Sep 25, 2023 8:35 pm

Tracking Your Net Worth (Excel Experts needed)

Post by Dufus »

[Topic is now in Personal Finance (Not Investing) - mod mkc]

I have an Excel Workbook that I use to track all our assets and liabilities. Every year I manually note the net on 12/31.

Is there a way to copy the net worth cell to another sheet at a regular interval so I can see more history without doing it manually?

You can do VBA based on a cell change, but I think that is based on a manual change, not a change due to other fields changing that are part of the total Sum. Feel free to correct me if I am wrong.

This site had a number of Excel wizards and I'm hoping someone can help me out. Thanks!
rk6
Posts: 159
Joined: Mon Mar 18, 2019 12:32 pm

Re: Tracking Your Net Worth (Excel Experts needed)

Post by rk6 »

I would use Workbook_Open to (every time you open the XLS), go to a "history" sheet, create a new row, put a date in column A, and copy the value from your source location to the same row column B.

https://stackoverflow.com/questions/534 ... book-opens
chassis
Posts: 2344
Joined: Tue Mar 24, 2020 4:28 pm

Re: Tracking Your Net Worth (Excel Experts needed)

Post by chassis »

Dufus wrote: Sat Jun 01, 2024 10:56 am I have an Excel Workbook that I use to track all our assets and liabilities. Every year I manually note the net on 12/31.

Is there a way to copy the net worth cell to another sheet at a regular interval so I can see more history without doing it manually?

You can do VBA based on a cell change, but I think that is based on a manual change, not a change due to other fields changing that are part of the total Sum. Feel free to correct me if I am wrong.

This site had a number of Excel wizards and I'm hoping someone can help me out. Thanks!
What keeps you from using your current process, manually noting the net on 12/31, in the cell of your choice?

I have been tracking net worth (all assets, all liabilities, all income sources, all expenses) in one Excel workbook for 30 years. It works.
bonesly
Posts: 1556
Joined: Mon Dec 18, 2017 9:28 pm
Location: WA

Re: Tracking Your Net Worth (Excel Experts needed)

Post by bonesly »

Dufus wrote: Sat Jun 01, 2024 10:56 am I have an Excel Workbook that I use to track all our assets and liabilities. Every year I manually note the net on 12/31.

Is there a way to copy the net worth cell to another sheet at a regular interval so I can see more history without doing it manually?

You can do VBA based on a cell change, but I think that is based on a manual change, not a change due to other fields changing that are part of the total Sum. Feel free to correct me if I am wrong.
I'm not sure exactly what you're trying to do, but I'd probably just have one worksheet, with a series of tabs for each year's net worth calculation (assets - liabilities) and then a summary tab showing the net worth history by year (could chart this too).

Here's a very simplistic example of what I mean. It may not provide the automation you're seeking (not clear to me), or perhaps you don't want the full history of your net worth calculation (bloat?), but perhaps it's a useful for ideas.
A Very Simple Net Worth History Sheet

I do use a reference to another sheet for my annual budget (Jan of year X links to the budget of year X-1 in Dec), which uses a formula something like this:

='./Budget/[Budget 2023.xlsx]Dec'!$H$7+C7+SUM(I7:K7), where C7 in the Budget 2024 sheet is a positive monthly allocation and I7:K7 represent negative spending that month on that line item, with a running balance from month to month (thus the need to refer to Dec of last year for Jan of this year).
Don't do what Bogleheads tell you. Listen to what we say, consider other sources, and make your own decisions, since you have to live with the risks & rewards (not us or anyone else).
JTravers
Posts: 27
Joined: Wed May 28, 2008 7:38 am

Re: Tracking Your Net Worth (Excel Experts needed)

Post by JTravers »

Not exactly sure what you’re trying to do, but newer versions of Excel have FILTER and SORT functions you can use in formulas to dynamically manipulate data.
gips
Posts: 1859
Joined: Mon May 13, 2013 5:42 pm

Re: Tracking Your Net Worth (Excel Experts needed)

Post by gips »

Could you give us an example of what you’d like to accomplish?
BogBod
Posts: 167
Joined: Tue Jan 01, 2019 5:14 pm

Re: Tracking Your Net Worth (Excel Experts needed)

Post by BogBod »

Is this what you are trying to do?:

https://www.automateexcel.com/formulas/ ... -workbook/
User avatar
WoodSpinner
Posts: 3545
Joined: Mon Feb 27, 2017 12:15 pm

Re: Tracking Your Net Worth (Excel Experts needed)

Post by WoodSpinner »

You can always reference a cell, row, or column from another Excel Workbook (file) to a different one.

Updates will be automatic on Windows and on the Mac (if both opened).
WoodSpinner
User avatar
Watty
Posts: 29300
Joined: Wed Oct 10, 2007 3:55 pm

Re: Tracking Your Net Worth (Excel Experts needed)

Post by Watty »

It has been years since I did any serious Excel spreadsheets but as I recall you can just have them both open at the same time and in one spreadsheet you can just enter '=' then click the cells you want in the other spreadsheet.

As I recall a problem with this is that if you ever need to move the spreadsheet or give someone a copy of it then it is easy to break the link.
JBTX
Posts: 11543
Joined: Wed Jul 26, 2017 12:46 pm

Re: Tracking Your Net Worth (Excel Experts needed)

Post by JBTX »

So don’t have just one tab, that you keep updating and overwriting, or don’t create a new tab each year?
toddthebod
Posts: 6785
Joined: Wed May 18, 2022 12:42 pm

Re: Tracking Your Net Worth (Excel Experts needed)

Post by toddthebod »

Dufus wrote: Sat Jun 01, 2024 10:56 am [Topic is now in Personal Finance (Not Investing) - mod mkc]
You can do VBA based on a cell change, but I think that is based on a manual change, not a change due to other fields changing that are part of the total Sum. Feel free to correct me if I am wrong.
To solve this problem, see here: https://stackoverflow.com/questions/409 ... ell-change

Personally, I would tie it to a button instead, so that you can click a button to save the value and date at the bottom of a list somewhere (and update the graph that I assume you made as well!)
EricGold
Posts: 706
Joined: Sat Mar 16, 2024 4:19 pm

Re: Tracking Your Net Worth (Excel Experts needed)

Post by EricGold »

My general approach is to keep one long list of running data, and then to either use filters, or more commonly a pivot table, to analyze.
Google has somewhat recently introduced 'slicers' that solve may such problems easily in their free Google Sheets
User avatar
Peter Foley
Posts: 5555
Joined: Fri Nov 23, 2007 9:34 am
Location: Lake Wobegon

Re: Tracking Your Net Worth (Excel Experts needed)

Post by Peter Foley »

Not exactly the question asked . . .

I track net worth on a separate excel tab. I also have an after tax version which discounts IRA/401k totals by applying our federal marginal tax rate and our state income tax rate. With the bulk of our savings in tax deferred this gives me a more realistic number.

We are of that age range where Roths were not available while we were working and so we did Roth conversions prior to SS benefits. Still the bulk is in tax deferred.
Topic Author
Dufus
Posts: 181
Joined: Mon Sep 25, 2023 8:35 pm

Re: Tracking Your Net Worth (Excel Experts needed)

Post by Dufus »

I apologize to all for the poor description of the issue. I just want to copy one cell from one worksheet onto another cell that would track history of that cell.

I like rk6's suggestion of using Workbook_Open but I'm having trouble with the simplest part. (What a maroon!) I can't seem to figure out how to insert a row into just the new worksheet and my search skills for this are lacking. I tried "Total History".Rows(1).Insert but that's not valid. I started with the code below but quickly realized the first two lines apply to both the original sheet "Allocation" and the new sheet "Total History". As a result, the 3rd line never gets populated correctly because the row value keeps getting incremented. I do get a new row and date in both sheets. How do I insert the new row and date into only the the worksheet "Total History"


Rows(1).Insert
Range("C1").Value = Date
Worksheets("Total History").Range("E1").Value = Worksheets("Allocation").Range("O128").Value


Thanks for your patience with me and for any pointers.
p1db
Posts: 92
Joined: Sun Dec 04, 2016 10:54 pm
Location: Northern California

Re: Tracking Your Net Worth (Excel Experts needed)

Post by p1db »

I have completely switched over to Google Sheets. I have tabs in a master spreadsheet, each one showing (1) Brokerage accounts, (2) IRAs and 401k, (3) Bank and Money market (4) Home equity.

I have a summary tab which shows data in 6 cells in one column. Once a month (usually last weekend of the month) I copy over contents of these cells into a new column in another tab. A plot showing these 4 items as well as total NW is automatically updated. I have been doing this every month for the last 7 years.

Tracking stocks, ETFs and Mutual funds is easy - Google sheets provides almost real time quotes with the googlefinance() function.

Automating seemed like a pain, since I had to anyway manually enter bank balances once a month, I decided to take the copy & paste approach.
LotsaGray
Posts: 1514
Joined: Sat Mar 25, 2023 2:08 pm

Re: Tracking Your Net Worth (Excel Experts needed)

Post by LotsaGray »

Dufus wrote: Sun Jun 02, 2024 7:30 pm I apologize to all for the poor description of the issue. I just want to copy one cell from one worksheet onto another cell that would track history of that cell.

I like rk6's suggestion of using Workbook_Open but I'm having trouble with the simplest part. (What a maroon!) I can't seem to figure out how to insert a row into just the new worksheet and my search skills for this are lacking. I tried "Total History".Rows(1).Insert but that's not valid. I started with the code below but quickly realized the first two lines apply to both the original sheet "Allocation" and the new sheet "Total History". As a result, the 3rd line never gets populated correctly because the row value keeps getting incremented. I do get a new row and date in both sheets. How do I insert the new row and date into only the the worksheet "Total History"


Rows(1).Insert
Range("C1").Value = Date
Worksheets("Total History").Range("E1").Value = Worksheets("Allocation").Range("O128").Value


Thanks for your patience with me and for any pointers.
While it will seldom generate optimal code for something this simple just run the macro recorder. Then copy paste that Xcode.
chassis
Posts: 2344
Joined: Tue Mar 24, 2020 4:28 pm

Re: Tracking Your Net Worth (Excel Experts needed)

Post by chassis »

Dufus wrote: Sun Jun 02, 2024 7:30 pm I apologize to all for the poor description of the issue. I just want to copy one cell from one worksheet onto another cell that would track history of that cell.

I like rk6's suggestion of using Workbook_Open but I'm having trouble with the simplest part. (What a maroon!) I can't seem to figure out how to insert a row into just the new worksheet and my search skills for this are lacking. I tried "Total History".Rows(1).Insert but that's not valid. I started with the code below but quickly realized the first two lines apply to both the original sheet "Allocation" and the new sheet "Total History". As a result, the 3rd line never gets populated correctly because the row value keeps getting incremented. I do get a new row and date in both sheets. How do I insert the new row and date into only the the worksheet "Total History"


Rows(1).Insert
Range("C1").Value = Date
Worksheets("Total History").Range("E1").Value = Worksheets("Allocation").Range("O128").Value


Thanks for your patience with me and for any pointers.
Read and watch some Excel tutorials, there are lots of them.
User avatar
K72
Posts: 452
Joined: Wed Dec 05, 2018 7:04 pm

Re: Tracking Your Net Worth (Excel Experts needed)

Post by K72 »

Dufus wrote: Sun Jun 02, 2024 7:30 pm I apologize to all for the poor description of the issue. I just want to copy one cell from one worksheet onto another cell that would track history of that cell.

I like rk6's suggestion of using Workbook_Open but I'm having trouble with the simplest part. (What a maroon!) I can't seem to figure out how to insert a row into just the new worksheet and my search skills for this are lacking. I tried "Total History".Rows(1).Insert but that's not valid. I started with the code below but quickly realized the first two lines apply to both the original sheet "Allocation" and the new sheet "Total History". As a result, the 3rd line never gets populated correctly because the row value keeps getting incremented. I do get a new row and date in both sheets. How do I insert the new row and date into only the the worksheet "Total History"


Rows(1).Insert
Range("C1").Value = Date
Worksheets("Total History").Range("E1").Value = Worksheets("Allocation").Range("O128").Value


Thanks for your patience with me and for any pointers.
Either Sheets("Total History").Rows(1).Insert or Worksheets("Total History").Rows(1).Insert should work. I agree with chassis about watching videos. Also, when I search for help, I always use "VBA" in the search text. Example: "VBA insert row in worksheet".
All we want are the facts...
Topic Author
Dufus
Posts: 181
Joined: Mon Sep 25, 2023 8:35 pm

Re: Tracking Your Net Worth (Excel Experts needed)

Post by Dufus »

Thanks all. It is very much appreciated.
Post Reply