Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Have a question about your personal investments? No matter how simple or complex, you can ask it here.
Post Reply
Topic Author
renue74
Posts: 1579
Joined: Tue Apr 07, 2015 7:24 pm

Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by renue74 » Wed Jul 29, 2015 5:04 pm

OK...I've slowly been creating my portfolio tracking Google doc. I've used a sample I found here or somewhere on the interwebs and have added some additional info and a new "retirement income projection" sheet, where you can change how many years to retirement and project income (The estimated income tax is not correct.) The Google finance formula is really super nice.

I'm sharing mine....you share yours.

Note: All figures are hypothetical and no personal details have been shared on this public google sheet.

https://docs.google.com/spreadsheets/d/ ... sp=sharing

lack_ey
Posts: 6691
Joined: Wed Nov 19, 2014 11:55 pm

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by lack_ey » Wed Jul 29, 2015 5:16 pm

Yeah, I have a Google Docs sheet kind of like that, but less color coded. I'm not sharing it (sorry) because it doesn't have anything this one doesn't and it takes more than a second of effort to scrub something clean to be publicly presentable.

By the way, if those portfolio holdings are legitimate, you should note that the international stock funds have nontrivial emerging markets exposure, so that $0 for emerging markets is not accurate. They're majority in developed ex-US but that's not all they have. You'd have to hardcode in or find some way to import a percentage breakdown of developed vs. emerging for those funds, though.

pierremonfrere
Posts: 156
Joined: Tue Jun 23, 2015 7:34 pm

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by pierremonfrere » Wed Jul 29, 2015 5:19 pm

That's a nice tracker! I might consider adapting it for my own portfolio. I've never used google docs before. Is there a way to sync the information or do you have to update all the info manually? That seems like it would take a while.

astrohip
Posts: 469
Joined: Tue Dec 21, 2010 4:29 pm
Location: Houston TX

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by astrohip » Wed Jul 29, 2015 5:30 pm

I use Quicken for this. It keeps track of all investments, all transactions, values, costs, etc. You can classify holdings with categories and other classes, to create a variety of reports. And graphs. Plus it can export, so if you really want a spreadsheet analysis, you can do that too. It costs about $30 every three years.

It's one of those "why would anyone re-invent the wheel for $10/year" decisions.
"Happiness is not about doing, it’s about being." - R Branson

Topic Author
renue74
Posts: 1579
Joined: Tue Apr 07, 2015 7:24 pm

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by renue74 » Wed Jul 29, 2015 5:55 pm

pierremonfrere wrote:That's a nice tracker! I might consider adapting it for my own portfolio. I've never used google docs before. Is there a way to sync the information or do you have to update all the info manually? That seems like it would take a while.
The daily NAV values update automatically. You only update the # of shares you own and cost basis. It's not bad to update.

mhalley
Posts: 7102
Joined: Tue Nov 20, 2007 6:02 am

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by mhalley » Wed Jul 29, 2015 5:56 pm

Dupe
Mike

Topic Author
renue74
Posts: 1579
Joined: Tue Apr 07, 2015 7:24 pm

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by renue74 » Wed Jul 29, 2015 5:57 pm

lack_ey wrote:Yeah, I have a Google Docs sheet kind of like that, but less color coded. I'm not sharing it (sorry) because it doesn't have anything this one doesn't and it takes more than a second of effort to scrub something clean to be publicly presentable.

By the way, if those portfolio holdings are legitimate, you should note that the international stock funds have nontrivial emerging markets exposure, so that $0 for emerging markets is not accurate. They're majority in developed ex-US but that's not all they have. You'd have to hardcode in or find some way to import a percentage breakdown of developed vs. emerging for those funds, though.
Yeah...I know the one VFWAX fund, I think has about 15 to 18% emerging markets. I'll need to look into splitting that out.

mervinj7
Posts: 906
Joined: Thu Mar 27, 2014 3:10 pm

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by mervinj7 » Wed Jul 29, 2015 6:43 pm

Wow, that's a great tracker! I don't think mine as anything more to add. I use the same google finance formulas to calculate the daily price. If I can get it cleaned up today, I'll post it.

trueblueky
Posts: 1504
Joined: Tue May 27, 2014 3:50 pm

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by trueblueky » Wed Jul 29, 2015 6:47 pm

One Excel sheet per year. It shows holdings and tracks net worth over time.

Travis1
Posts: 49
Joined: Sun Jul 19, 2015 11:37 pm

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by Travis1 » Wed Jul 29, 2015 6:56 pm

That is incredible. As a beginner, I'm very impressed by your dedication. Any tips for someone who might want to create a system like that of their own?

User avatar
backpacker
Posts: 1620
Joined: Mon Sep 22, 2014 2:17 pm

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by backpacker » Wed Jul 29, 2015 6:57 pm

I used to have daily price changes listed on my Google Doc. Then I thought, daily prices changes are just noise. Why the heck am I tracking noise? So I got rid of them.

Then I realized that there was no reason for me to check my total balance on a regular balance. So I got rid of that too. I calculate it by hand maybe once a year.

Now, all my spreadsheet tells me is how my allocation percentages compare to my target allocation percentages. And how much money I need to move to rebalance.

The majesty of simplicity. :beer

Topic Author
renue74
Posts: 1579
Joined: Tue Apr 07, 2015 7:24 pm

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by renue74 » Wed Jul 29, 2015 7:08 pm

Travis1 wrote:That is incredible. As a beginner, I'm very impressed by your dedication. Any tips for someone who might want to create a system like that of their own?
The google spreadsheet I shared can be yours. You can just copy it into your own Google Sheets and change what you like...just make sure that you don't "share" your sheet.

fidobogo
Posts: 154
Joined: Thu Mar 19, 2015 8:23 pm

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by fidobogo » Wed Jul 29, 2015 7:19 pm

GnuCash tracks all my investments, and all my other assets, my liabilities, etc. With data feeds for current stock/fund/commodity/currency values. It tracks each purchase and sale and distribution, including dividend reinvestment, and the reports will show calculated info like my basis and ROR.

When I held physical PM coins/bullion (don't anymore), it also had it tracking the current melt value of the various pieces I held, and including that in my net worth number. (I didn't want to hassle with updating premium info on the bullion/coins, so I just showed melt.)

dbr
Posts: 29417
Joined: Sun Mar 04, 2007 9:50 am

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by dbr » Thu Jul 30, 2015 7:20 am

Travis1 wrote:That is incredible. As a beginner, I'm very impressed by your dedication. Any tips for someone who might want to create a system like that of their own?
My tip would be get on a couple of different spreadsheet applications and start trying different things until you become fairly fluent and experienced and work your way to what is useful to you. Personally I find trying to use/adapt other people's spreadsheets to be tedious and time consuming and would rather start from scratch myself.

therivler1
Posts: 30
Joined: Sun Aug 17, 2008 8:53 am

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by therivler1 » Thu Jul 30, 2015 8:06 am

Much nicer than mine.

2 points already mentioned:

1 - emerging markets. total int'l funds have a non-trivial amount, IIRC around 20%
2 - rebalancing. I have a few extra columns so that I can play around with buying/selling/new money and see how that would affect my overall allocation as compared to my current AA.
3 - I have a few fields that sum up my allocation as check fields. I have messed up calculations before and ended up with > 100% allocation. It is helpful to see exactly 100% in a few places to know that everything is correct. I have 4 of them, overall AA before & after rebalancing, and sum of individual funds before/after rebalancing.

Bonhomie
Posts: 65
Joined: Thu Aug 22, 2013 2:26 pm

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by Bonhomie » Thu Jul 30, 2015 3:40 pm

renue74 wrote:The Google finance formula is really super nice.
Super nice! Thanks for the share. Now time to geek out...

letsgobobby
Posts: 12114
Joined: Fri Sep 18, 2009 1:10 am

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by letsgobobby » Thu Jul 30, 2015 4:00 pm

Mine is similar but like backpacker, the two most important numbers are highlighted at the top, while less important numbers are hidden at the bottom or not on the sheet at all.

The important numbers are:

Stock/bond allocation
International vs domestic stock allocation

The less important numbers are:
Total portfolio balance
Tax allocation balances (Roths vs pretax vs taxable)

Daily changes are irrelevant to me and not on my spreadsheet.

I check balances quarterly and update share quantities at that time.

One thing I've learned is that the important numbers don't change very ugh very often. I haven't rebalanced at all in four years. I could probably check annually and there would be no harm. In fact that is my goal: to check only annually or whenever stocks decline by 15% or more. Emotionally, not there yet.

JaySayms
Posts: 255
Joined: Mon Feb 09, 2015 6:59 pm

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by JaySayms » Thu Jul 30, 2015 4:22 pm

https://docs.google.com/spreadsheets/d/ ... edit#gid=3


This is the original template I used which I found online. Pretty basic. I've since added more to it because I have several retirement accounts now.


Jay

User avatar
in_reality
Posts: 4529
Joined: Fri Jul 12, 2013 6:13 am

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by in_reality » Thu Jul 30, 2015 11:29 pm

My important numbers are:

Stock/bond allocation
International dev vs emerging vs domestic stock allocation
9 box percentages (broken down into Int dev, emerging and domestic)
ave bond duration
percents of bonds in Gov., muni, junk etc.
Total portfolio balance (with 10%, 25%, 50% equity loss)

Requires:
update on share totals (I export from sig fig and it's easy to copy into place)
update on 9 box (I enter holding into morningstar instant x-ray and copy the rows fund by fund)
update bond duration info (doesn't change so much so often)

Additional functionality:
entering $$ to any position in a "new money" column will show me how my important numbers change

I never know you could adjust the color of a column by formula to show green/red gain/loss.

Topic Author
renue74
Posts: 1579
Joined: Tue Apr 07, 2015 7:24 pm

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by renue74 » Fri Jul 31, 2015 7:11 am

in_reality wrote:I never know you could adjust the color of a column by formula to show green/red gain/loss.
The color formatting is a "conditional format," such as "if number is between X an X, highlight in color X."

I didn't know that could happen until I started using Google sheets.

User avatar
in_reality
Posts: 4529
Joined: Fri Jul 12, 2013 6:13 am

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by in_reality » Fri Jul 31, 2015 7:20 am

renue74 wrote:
in_reality wrote:I never know you could adjust the color of a column by formula to show green/red gain/loss.
The color formatting is a "conditional format," such as "if number is between X an X, highlight in color X."

I didn't know that could happen until I started using Google sheets.
Everything I do is in google sheets and still I didn't know ... thanks ....it'll come in handy to show how off my AA is! (Emphasis!!!!)

User avatar
Rainier
Posts: 1429
Joined: Thu Jun 14, 2012 5:59 am

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by Rainier » Fri Jul 31, 2015 8:38 am

I think I just requested permission to view your sheet.

Topic Author
renue74
Posts: 1579
Joined: Tue Apr 07, 2015 7:24 pm

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by renue74 » Fri Jul 31, 2015 8:43 am

Rainier wrote:I think I just requested permission to view your sheet.
The sheet I shared is a "dummy" sheet based off my personal one. I set this dummy sheet to be editable by anybody and I saw where somebody changed that access. :)

Here is the fully editable sheet. I actually based my 2nd tab (Retirement income) on a conservative 3%/year increase, but you can play with the numbers as you like.

https://docs.google.com/spreadsheets/d/ ... sp=sharing

User avatar
3wood
Posts: 93
Joined: Sun Apr 28, 2013 8:21 pm

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by 3wood » Fri Jul 31, 2015 8:55 am

Thanks for sharing. :beer

Lafder
Posts: 3954
Joined: Sat Aug 03, 2013 7:56 pm
Location: East of the Rio Grande

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by Lafder » Fri Jul 31, 2015 8:59 am

Wow. I am glad you all enjoy that. The info I can get on the Vanguard or Fidelity website is good enough for me :)

To each their own......

lafder

User avatar
53timr
Posts: 216
Joined: Sat Dec 27, 2014 4:27 pm

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by 53timr » Fri Jul 31, 2015 11:27 am

I thought I would share the Google spreadsheet I use to track my portfolio. It takes a lot of work to enter the information but the result is well worth it as I can see exactly how my portfolio is performing each year.

https://docs.google.com/spreadsheets/d/ ... sp=sharing
“I take my investment advice from my dentist, because he’s just as likely to lose me money as a financial advisor.” | ― Jarod Kintz, This Book Title is Invisible

mav12
Posts: 32
Joined: Tue Nov 07, 2017 5:36 pm

Re: Tracking Portfolio - What's your XLS, Google Sheet, or whatever look like?

Post by mav12 » Thu Jul 11, 2019 11:39 pm

renue74 wrote:
Wed Jul 29, 2015 7:08 pm
Travis1 wrote:That is incredible. As a beginner, I'm very impressed by your dedication. Any tips for someone who might want to create a system like that of their own?
The google spreadsheet I shared can be yours. You can just copy it into your own Google Sheets and change what you like...just make sure that you don't "share" your sheet.
Thank you, renue74. For some reason YTD data shows errors

Post Reply