spreadsheet [to organize a portfolio]

Have a question about your personal investments? No matter how simple or complex, you can ask it here.
Post Reply
1year23
Posts: 31
Joined: Fri Apr 01, 2016 8:31 am

spreadsheet [to organize a portfolio]

Post by 1year23 » Thu Aug 18, 2016 9:33 am

I would like to organize my finances on a spreadsheet. I am not looking for anything too fancy but something easily readable and that can be modified over time. Is it overkill to learn an Apple version of Excel or is there a simpler way? What have Bogleheads found works best? Thanks!

User avatar
oldcomputerguy
Posts: 3437
Joined: Sun Nov 22, 2015 6:50 am
Location: In the middle of five acres of woods

Re: spreadsheet

Post by oldcomputerguy » Thu Aug 18, 2016 9:36 am

I've done my budget on the open-source office suite LibreOffice for years. Works very much like MS Office. Very handy tool. You might also check your brick-and-mortar bank to see if they provide an aggregator service (similar to Mint); many of those implementations provide budgeting tools.
It’s taken me a lot of years, but I’ve come around to this: If you’re dumb, surround yourself with smart people. And if you’re smart, surround yourself with smart people who disagree with you.

BogleMelon
Posts: 1476
Joined: Mon Feb 01, 2016 11:49 am

Re: spreadsheet

Post by BogleMelon » Thu Aug 18, 2016 9:36 am

I am an Excel guru, however I failed to do personal finances with a spreadsheet (However I am an accountant!). What really worked best for me was Ynab 4
"One of the funny things about stock market, every time one is buying another is selling, and both think they are astute" - William Feather

User avatar
patrick013
Posts: 2406
Joined: Mon Jul 13, 2015 7:49 pm

Re: spreadsheet

Post by patrick013 » Thu Aug 18, 2016 9:52 am

There are some spreadsheets in the Wiki. Probably need
some minor adjustments but do give a good idea of the
reporting a spreadsheet can do.
age in bonds, buy-and-hold, 10 year business cycle

JohnFiscal
Posts: 615
Joined: Mon Jan 06, 2014 4:28 pm
Location: Florida

Re: spreadsheet

Post by JohnFiscal » Thu Aug 18, 2016 9:58 am

I have used Excel for Windows since around 1995 to work with all my finances. In the beginning I tried using Quicken and Microsoft Money (back when this was an application installed locally). I found that I preferred using Excel as I could customize it to my needs. I have thought about using a full-blown database software instead.

I have heard that Excel for Mac is not quite up to the level of Excel for PC in current implementations. That right there dissuades me from moving to a Mac.

This work has helped me to learn how to use Excel better in my job. And vice versa. Certainly it's a lot of work (well, not so much once you know Excel well) but the benefits to me outweigh the simplicity of going to some commercial software.

There are a number templates available on-line for free or at some cost. In the end I think it's best just to make your own to your own preferences.

User avatar
Just sayin...
Posts: 205
Joined: Tue Oct 09, 2007 10:12 am

Re: spreadsheet

Post by Just sayin... » Thu Aug 18, 2016 11:23 am

Up until just recently, I tried to accomplish much of my budgeting/planning using Google Sheets (after all, it's "free"). A recent 2-day internet outage at my home has cured me of that notion, and I am contemplating a switch to the free Libre Office, or the paid Microsoft Office. Just be aware that automatically getting stock quotes into either one of these is considerably more difficult than with Sheets (if not impossible). Why Libre Office and Excel don't do this is beyond me...

User avatar
Tortuga
Posts: 115
Joined: Sat Aug 16, 2008 9:51 pm
Location: Houston, Texas

Re: spreadsheet

Post by Tortuga » Thu Aug 18, 2016 11:32 am

Here is the Wiki discussion:

https://www.bogleheads.org/wiki/Using_a ... _portfolio

Tortuga

hcj
Posts: 261
Joined: Wed Mar 26, 2014 2:21 pm

Re: spreadsheet

Post by hcj » Thu Aug 18, 2016 12:41 pm

I use google sheets. It's free and does what I need it to do. But I'm doing pretty simple stuff I think.

pshonore
Posts: 6422
Joined: Sun Jun 28, 2009 2:21 pm

Re: spreadsheet

Post by pshonore » Thu Aug 18, 2016 1:14 pm

Just sayin... wrote:Up until just recently, I tried to accomplish much of my budgeting/planning using Google Sheets (after all, it's "free"). A recent 2-day internet outage at my home has cured me of that notion, and I am contemplating a switch to the free Libre Office, or the paid Microsoft Office. Just be aware that automatically getting stock quotes into either one of these is considerably more difficult than with Sheets (if not impossible). Why Libre Office and Excel don't do this is beyond me...
For years there was an "add-in" that worked I think with MS Money to look up prices. Of course every time MS Money got "tweaked" it stopped working. For the last couple of years I've used this:

http://pmstockquote.codeplex.com/

Works well with Office 10 and Office 16. YMMV

Dashboard
Posts: 26
Joined: Tue Jul 05, 2016 11:23 am

Re: spreadsheet

Post by Dashboard » Thu Aug 18, 2016 1:30 pm

Just sayin... wrote:Up until just recently, I tried to accomplish much of my budgeting/planning using Google Sheets (after all, it's "free"). A recent 2-day internet outage at my home has cured me of that notion, and I am contemplating a switch to the free Libre Office, or the paid Microsoft Office. Just be aware that automatically getting stock quotes into either one of these is considerably more difficult than with Sheets (if not impossible). Why Libre Office and Excel don't do this is beyond me...
Just FYI, google docs all have offline capabilities where your files can be accessed offline and then will update online once you are reconnected. I do this on airplanes all the time.
https://support.google.com/drive/answer ... ktop&hl=en

User avatar
swimirvine
Posts: 368
Joined: Tue May 28, 2013 7:51 am

Re: spreadsheet

Post by swimirvine » Thu Aug 18, 2016 1:41 pm

Just sayin... wrote:Up until just recently, I tried to accomplish much of my budgeting/planning using Google Sheets (after all, it's "free"). A recent 2-day internet outage at my home has cured me of that notion, and I am contemplating a switch to the free Libre Office, or the paid Microsoft Office. Just be aware that automatically getting stock quotes into either one of these is considerably more difficult than with Sheets (if not impossible). Why Libre Office and Excel don't do this is beyond me...
Excel will do this and a lot more with the SMF Add-in. Just google it and download the file and add it to Excel. Then you have access to 1000s of Stock Market Functions (SMF) from multiple sources.
The way I invest my money is not the right way to invest, it's the right way for ME to invest.

red5
Posts: 777
Joined: Sun Apr 01, 2012 4:42 pm

Re: spreadsheet

Post by red5 » Fri Aug 19, 2016 4:33 am

I recently discovered an online and free version of Excel. I haven't really checked it out yet but it might be worth it.

Dantes
Posts: 219
Joined: Wed Feb 25, 2015 6:38 pm

Re: spreadsheet

Post by Dantes » Fri Aug 19, 2016 5:52 am

I use Numbers,which was included for free with my MacBook Pro. I use it for both portfolio management and long-term planning.

JohnFiscal
Posts: 615
Joined: Mon Jan 06, 2014 4:28 pm
Location: Florida

Re: spreadsheet

Post by JohnFiscal » Thu Aug 25, 2016 9:21 pm

Just sayin... wrote:... Google Sheets (after all, it's "free"). A recent 2-day internet outage at my home has cured me of that notion, and I am contemplating a switch to the free Libre Office, or the paid Microsoft Office. Just be aware that automatically getting stock quotes into either one of these is considerably more difficult than with Sheets (if not impossible). Why Libre Office and Excel don't do this is beyond me...
(1) It is possible, although really difficult for non-programmers to get stock prices into Excel "automatically". I do this a quick and easy way: I have a Google Sheet set up for my funds prices, Dow, etc. Then when I need to I simply open (updates automatically) and copy/paste the information into my Excel spreadsheet. Easy. I have found that the functions in Google Sheets seem not to get fund prices updated as quickly as websites. I find that I may have to wait several hours or overnight to get the closing prices. Anyway, this method is good enough for me, rather than manually typing in prices.

(2) I use Google Sheets a lot to provide budgeting tools for various family members. I have learned to detest Sheets over this time, even though I still maintain these documents monthly. For my own use I keep a duplicate Excel workbook and share information back and forth.

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

Re: spreadsheet [to organize a portfolio]

Post by in_reality » Thu Aug 25, 2016 9:56 pm

I rely on Google Sheets.

Examples:

(1) pull in price of VTSAX -Total Market Admiral

=GOOGLEFINANCE("VTI",'PRICE")

actually I have VTSAX and price written in cells so use

=GOOGLEFINANCE(B11,D1)

(2) pull in Credit Quality of VWEAX -Vanguard's high yield bond fund

=query(ImportHtml("http://portfolios.morningstar.com/fund/ ... ture=en_US", "table", 5), "select Col1, Col2")

(3) Pull in geographic exposure of VT (total world) - not per country data unfortunately but rather by region

=query(ImportHtml("http://portfolios.morningstar.com/fund/ ... ctcode=COM", "table", 7), "select Col1, Col2")

(4) Pull in sector info to see sector tilts of a VBR (Vanguard Small Value) (also to calculate total REIT holdings for instance)

=query(ImportHtml("http://portfolios.morningstar.com/fund/ ... ctcode=COM", "table", 6), "select Col2, Col3")


Of course you can do all of that just looking at Morningstar. Also, it's not possible to pull in 9 box information on funds. Well you can but it's complicated and not directly from google sheets.

I use ETFs and data is updated soon. I think another poster maybe had to wait until the mutual fund data was calculated and reported for the day.

Wagnerjb
Posts: 7203
Joined: Mon Feb 19, 2007 8:44 pm
Location: Houston, Texas

Re: spreadsheet [to organize a portfolio]

Post by Wagnerjb » Fri Aug 26, 2016 3:50 pm

I have found that a simple spreadsheet works fine. I began with Lotus 123 back in the 1980's, moved to Excel and recently moved to Numbers when I got a Mac. I can customize the spreadsheet to show the subtotals that I want, I can customize the spreadsheet to show the allocation as I want, I can customize the spreadsheet to show tax basis as I want, etc.

Best wishes.
Andy

User avatar
nisiprius
Advisory Board
Posts: 36852
Joined: Thu Jul 26, 2007 9:33 am
Location: The terrestrial, globular, planetary hunk of matter, flattened at the poles, is my abode.--O. Henry

Re: spreadsheet [to organize a portfolio]

Post by nisiprius » Fri Aug 26, 2016 4:03 pm

I've used whatever was the current or almost-current version of Excel for the Mac since maybe about 1988. Excel in my opinion is quite good--not great, but good--despite it being from Microsoft.

LibreOffice--well, I worked for a Unix-centric company for four years which started out with OpenOffice and feel that I'm mildly familiar with it, and it is what it is. It's perfectly capable, and it's not "their" fault that it does not interoperate well with Excel, but it doesn't. If your Excel sheet is a column of currency values in dollars and cents with a total at the bottom, it's fine, but if it has anything fancy like macros, or if appearance is important (pretty tables), it's a problem. Furthermore, I was personally caught and whipsawed by the OpenOffice-to-LibreOffice transition. There was a period of about a year when stuff I half-counted-on was broken, because the people maintaining it had run away from Oracle, and of course no way to get a bug fixed except to fix it yourself.

I've used Google Docs fairly seriously for two projects. It is, by the way, _sensational_ for getting Spanish lessons online from instructors overseas, you can both open a Google document, you can type stuff in, the instructor can correct it in real-time, etc. But there are all kinds of random weird glitches. No matter how clever they get about using Web 2.0 and AJAX and so forth, it's not equivalent to a real application--and it is exquisitely sensitive to any issues at all about how your browser handles JavaScript. (It's the usual bait-and-switch: it works with everything, except it doesn't, and if you point out that it doesn't it's your fault for not using Chrome).
Annual income twenty pounds, annual expenditure nineteen nineteen and six, result happiness; Annual income twenty pounds, annual expenditure twenty pounds ought and six, result misery.

Joel
Posts: 210
Joined: Tue Apr 30, 2013 12:27 am

Re: spreadsheet [to organize a portfolio]

Post by Joel » Fri Aug 26, 2016 4:44 pm

This discussion is tempting me to setup something to pull stock prices into my spreadsheet.

Even if the prices are updated automatically, I still have the problem of purchases of shares needing to be updated.

I'm posting to think about it some more.

I currently have an excel spreadsheet where I update my account balances periodically and make adjustments to my new contributions when the allocations need rebalancing.
My investment policy statement: https://www.bogleheads.org/forum/viewtopic.php?t=190093

retiredjg
Posts: 34161
Joined: Thu Jan 10, 2008 12:56 pm

Re: spreadsheet [to organize a portfolio]

Post by retiredjg » Fri Aug 26, 2016 4:49 pm

Oh……I feel SSSSSOOOOOOOO old! :?

I use a pencil and paper and it is about as fast as entering the values into a spreadsheet.

JohnFiscal
Posts: 615
Joined: Mon Jan 06, 2014 4:28 pm
Location: Florida

Re: spreadsheet [to organize a portfolio]

Post by JohnFiscal » Fri Aug 26, 2016 8:31 pm

Joel wrote:...I still have the problem of purchases of shares needing to be updated.
I recently completed (and tweaked further and further) a utility in Excel to import my Vanguard transactions (fund purchases, distributions, etc) and update the fund account records in my main Excel workbook. I can download all the Vanguard information in a "csv" file format that is readable by Excel. I had to write my own VBA utilities to update the sheets in my main workbook (one sheet for each ownership/fund combo...kept in a ledger-like format).

This was on my radar for years but it became pretty urgent to me over a year ago when I had to manually update around 20 transactions in various funds in one month. This was really tedious.

It took me awhile to create the program but it was really worth it. Most months over time there have been only 3-5 transactions...distributions from bond funds and money market (now run down to about "0"). But some funds have quarterly distributions, others have semi-annual distributions, and the rest have annual distributions. And the distributions for a fund may consist of 3 transactions: dividend, STCG, and LTCG. On top of this I have moved a lot of money over to Vanguard with resulting proliferation of account/fund combos.

So the proliferation of transactions is exacerbated due to the account ownership: joint, spouse's Roth, my Roth, my IRA, my Rollover IRA, my inherited IRA. The same funds often show up in the different ownerships. I have 14 different ownership/fund combinations even though I only have 6 different actual funds.

I am really enjoying my new utility to update my transactions. What I would expect to take an hour or more (much more for end of the year) now takes a few seconds! And it has self-checking features.

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

Re: spreadsheet [to organize a portfolio]

Post by in_reality » Fri Aug 26, 2016 8:48 pm

Joel wrote:This discussion is tempting me to setup something to pull stock prices into my spreadsheet.

Even if the prices are updated automatically, I still have the problem of purchases of shares needing to be updated.

I'm posting to think about it some more.

I currently have an excel spreadsheet where I update my account balances periodically and make adjustments to my new contributions when the allocations need rebalancing.
I download holdings in CVS. Then I open it and copy the table and past it into a google sheet. The only trick is to make sure the columns haven't changed order so I have tickers in column A, and paste into column B. Column B is tickers from the CVS so it's easy to make sure they match.

Actually, I often just do it by fund after I know it's paid a dividend. A quick check of transactions let's me know if there is a change.

Joel
Posts: 210
Joined: Tue Apr 30, 2013 12:27 am

Re: spreadsheet [to organize a portfolio]

Post by Joel » Fri Aug 26, 2016 9:19 pm

having to login and download the csv is just as much work as login and type the new balance. Possibly longer since you may have to mess with formatting and such
My investment policy statement: https://www.bogleheads.org/forum/viewtopic.php?t=190093

larklea
Posts: 225
Joined: Sun Dec 26, 2010 2:48 pm

Re: spreadsheet [to organize a portfolio]

Post by larklea » Sat Aug 27, 2016 7:07 am

+1 Google Sheets. I keep a total portfolio sheet, primarily developed to give me asset allocation and expense ratio across all accounts.

I use the 'sunset' version of MIcrosoft Money plus also - for net worth, bank account reconcilations.

https://support.microsoft.com/en-us/kb/2118008

carolinaman
Posts: 3288
Joined: Wed Dec 28, 2011 9:56 am
Location: North Carolina

Re: spreadsheet [to organize a portfolio]

Post by carolinaman » Sat Aug 27, 2016 7:14 am

I have a simple Excel spreadsheet that lists each investment, current shares, share price, share value, return YTD and beginning of year investment. I can adjust based upon any buys and sells during the year. I then roll up to a summary that lists totals by category and percentage. I have consolidated my investments a lot in recent years but this approach works well unless you have a lot of changes during the year. I update it monthly.

You could do same with other simpler spreadsheet products.

Post Reply