Page 1 of 1

spreadsheet [to organize a portfolio]

Posted: Thu Aug 18, 2016 9:33 am
by 1year23
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!

Re: spreadsheet

Posted: Thu Aug 18, 2016 9:36 am
by oldcomputerguy
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.

Re: spreadsheet

Posted: Thu Aug 18, 2016 9:36 am
by BogleMelon
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

Re: spreadsheet

Posted: Thu Aug 18, 2016 9:52 am
by patrick013
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.

Re: spreadsheet

Posted: Thu Aug 18, 2016 9:58 am
by JohnFiscal
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.

Re: spreadsheet

Posted: Thu Aug 18, 2016 11:23 am
by Just sayin...
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...

Re: spreadsheet

Posted: Thu Aug 18, 2016 11:32 am
by Tortuga
Here is the Wiki discussion:

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

Tortuga

Re: spreadsheet

Posted: Thu Aug 18, 2016 12:41 pm
by hcj
I use google sheets. It's free and does what I need it to do. But I'm doing pretty simple stuff I think.

Re: spreadsheet

Posted: Thu Aug 18, 2016 1:14 pm
by pshonore
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

Re: spreadsheet

Posted: Thu Aug 18, 2016 1:30 pm
by Dashboard
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

Re: spreadsheet

Posted: Thu Aug 18, 2016 1:41 pm
by swimirvine
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.

Re: spreadsheet

Posted: Fri Aug 19, 2016 4:33 am
by red5
I recently discovered an online and free version of Excel. I haven't really checked it out yet but it might be worth it.

Re: spreadsheet

Posted: Fri Aug 19, 2016 5:52 am
by Dantes
I use Numbers,which was included for free with my MacBook Pro. I use it for both portfolio management and long-term planning.

Re: spreadsheet

Posted: Thu Aug 25, 2016 9:21 pm
by JohnFiscal
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.

Re: spreadsheet [to organize a portfolio]

Posted: Thu Aug 25, 2016 9:56 pm
by in_reality
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.

Re: spreadsheet [to organize a portfolio]

Posted: Fri Aug 26, 2016 3:50 pm
by Wagnerjb
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.

Re: spreadsheet [to organize a portfolio]

Posted: Fri Aug 26, 2016 4:03 pm
by nisiprius
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).

Re: spreadsheet [to organize a portfolio]

Posted: Fri Aug 26, 2016 4:44 pm
by Joel
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.

Re: spreadsheet [to organize a portfolio]

Posted: Fri Aug 26, 2016 4:49 pm
by retiredjg
Oh……I feel SSSSSOOOOOOOO old! :?

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

Re: spreadsheet [to organize a portfolio]

Posted: Fri Aug 26, 2016 8:31 pm
by JohnFiscal
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.

Re: spreadsheet [to organize a portfolio]

Posted: Fri Aug 26, 2016 8:48 pm
by in_reality
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.

Re: spreadsheet [to organize a portfolio]

Posted: Fri Aug 26, 2016 9:19 pm
by Joel
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

Re: spreadsheet [to organize a portfolio]

Posted: Sat Aug 27, 2016 7:07 am
by larklea
+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

Re: spreadsheet [to organize a portfolio]

Posted: Sat Aug 27, 2016 7:14 am
by carolinaman
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.