Personal Investment Returns Spreadsheet (PIRS)

[scoothome]

Version 8.0 has been released. Links in the OP have also been updated.

Main Google Sheet with only one account populated (less data to clear when entering your own data).
Demo Google Sheet with multiple accounts populated to illustrate how it works.
Main Excel workbook with one account populated.
Demo Excel workbook with multiple accounts populated to illustrate how it works.

• Introduces macros (Excel) and Apps Script (Google Sheets) to automate data transfer from previous populated PIRS file. For Excel you need to enable macros when opening the file. For Google Sheets, you will need to authorize it to access your Google Drive. The Google Sheets version is not as elegant as the Excel version because I had to break up the scripts to stay within the 6 minute script execution time limit. The GS version is very slow, it takes about 10 minutes to transfer the DEMO data compared to 40 seconds for the Excel version. If/once I figure out how to make the Google Sheets version more efficient (it seems that I have to reduce the number of API calls) I'll restore the parallelism.
• Bug fix for Growth of $10k on portfolio sheet (v7.0 fixed account sheets, but I forgot the portfolio sheet). BTW, I don't have confidence that the Growth of $10k chart is accurate for data series that have large cashflows relative to the balance. I plan to reexamine this in the future.

Excel version: on sheet MyPortfolio, click the blue button to open a file dialog, select the previous PIRS file, and transfer data.


Google Sheets version: more steps are required. On sheet MyPortfolio, when you click the first blue button you will be asked to authorize the PIRS script. See README.


After authorizing the PIRS script, you will be asked to enter the previous PIRS file ID. This can be found in the URL of the file after the /d/ and before /edit. I briefly looked at trying to use the Google Picker API to open a file dialog but gave up for now.


You must wait for the blue "Copy MyPortfolio" button and the instruction text above it to disappear before proceeding to the next step.


For the next step, you will click the "Copy Next 5 Accounts" button as many times as you need to transfer all of your account sheet data, waiting each time for the copy action to complete before clicking it again. To create the DEMO file from the main file, I clicked it twice (8 accounts, 5 copied on first click, wait for script to complete, then 3 on second click). Once the second blue button has disappeared, you click the third button to finish the script.

The GS version is painfully slow. I can manually copy/paste the data in about half the time as the Apps Script. If anyone has suggestions on how to speed it up to get closer to the 40 seconds that the Excel version takes, please let me know.

[scoothome]

Version 9.0 has been released. Links in the OP have also been updated.

Main Google Sheet with only one account populated (less data to clear when entering your own data).
Demo Google Sheet with multiple accounts populated to illustrate how it works.
Main Excel workbook with one account populated.
Demo Excel workbook with multiple accounts populated to illustrate how it works.

• Added sheet "Inflation", which currently requires user to manually update once per month. If I figure out how to update programmatically, I'll hide the sheet.
• Sheet "Returns": added real (i.e., inflation adjusted) returns in addition to nominal returns.

Sheet "Inflation": see the instructions on the sheet. User will need to add number in highlighted cell when the Bureau of Labor Statistics updates their CPI data (for example, November data should be posted December 11; see update schedule here.) Calculations use 0% inflation for the missing month.


Sheet "Returns": nominal returns data.


Real (inflation adjusted) returns data.


Annual portfolio real returns chart. Account returns chart is similar.


Annual portfolio inflation chart. Account inflation chart is similar. (Note that the MWRR/TWRR and Real/Nominal chart labels don't really apply to the inflation chart, but it was easier to leave it)


Note for Google Sheet users: for the DEMO data, I had to change the initial IRR rate guess parameter from 0.005 to 0.004 to get convergence on the IRR calculation for Her401k. Also, I haven't looked at how to speed up GS yet. Excel is much faster copying data from previous version.

[scoothome]

Version 9.1 has been released. Links in the OP have also been updated.

Main Google Sheet with only one account populated (less data to clear when entering your own data).
Demo Google Sheet with multiple accounts populated to illustrate how it works.
Main Excel workbook with one account populated.
Demo Excel workbook with multiple accounts populated to illustrate how it works.

• Returns sheet: Bug fix for annual account return or inflation chart to use same years for both charts.
• Google Sheets version, Balances sheet: restored missing x-axis labels for account contributions and growth charts.

Note that the scripts in version 9.1 assume you are updating from version 9.0. If instead you are updating from version 8.0, you will need to change a line of code to subtract 9 instead of 10 for the number of non-account sheets.

For Excel, see line 84 of the macro (in sub CopyData):

Code: Select all

numAccountSheets = oldFile.Sheets.Count - 10

For GS, see line 14 of script copyAccountData.gs:

Code: Select all

numAccountSheets = numAccountSheets - 10;

[scoothome]

Note that the scripts in version 9.1 assume you are updating from version 9.0. If instead you are updating from version 8.0, you will need to change a line of code to subtract 9 instead of 10 for the number of non-account sheets.

FYI, I've updated PIRS 9.1 (in-line) to calculate the number of account sheets in the old file irrespective of PIRS version, so you don't have to mess with the code to adjust number of non-account sheets for this or future releases. No action required if you've already updated to the latest version.

[scoothome]

Here's a preview of version 10.0, which adds the ability to specify a benchmark (for now, limited to a single mutual fund). I will release version 10.0 in January after gathering December data.

For the following screenshots, I used the DEMO data with accounts "HerTaxable" and "HisTaxable" turned off. The green line shows what the balance would have been had the cashflows been invested entirely in the benchmark fund.

Here the benchmark fund is set to VSMGX (Vanguard LifeStrategy Moderate Growth Fund Investor Shares):


Here the benchmark fund is set to VFINX (Vanguard 500 Index Fund Investor Shares):


This is just one example plot. PIRS also calculates and plots the benchmark fund maximum % and $ drawdowns, annual balances and cashflows, annualized rolling returns, and annual returns using Money-Weighted Rate of Return (MWRR) and Time-Weighted Rate of Return (TWRR) methodologies.

[scoothome]

Version 10.0 has been released.

Main Google Sheet with only one account populated (less data to clear when entering your own data).
Demo Google Sheet with multiple accounts populated to illustrate how it works.
Main Excel workbook with one account populated.
Demo Excel workbook with multiple accounts populated to illustrate how it works.

• Added sheet "Benchmarks" (and two hidden sheets) and associated calculations for balances and returns. See the README sheet for how to update/add benchmark data. For now, benchmarks are limited to a single mutual fund and comparison is made to the total portfolio only.
• Bug fix for flagging extraneous inputs.
• Miscellaneous clean-up.

User can use included benchmark funds or add their own following the instructions on the README sheet. I included benchmark funds with relatively long histories:

• VFINX: Vanguard 500 Index Investor
• VTSMX: Vanguard Total Stock Mkt Idx Inv
• VGTSX: Vanguard Total Intl Stock Index Inv
• VTWIX: Vanguard Total World Stock Index I
• VBMFX: Vanguard Total Bond Market Index Inv
• VIPSX: Vanguard Inflation-Protected Secs Inv
• VASGX: Vanguard LifeStrategy Growth Inv
• VSMGX: Vanguard LifeStrategy Mod Gr Inv
• VSCGX: Vanguard LifeStrategy Cnsrv Gr Inv
• VASIX: Vanguard LifeStrategy Income Inv

Here's a snippet of the Benchmarks sheet:


User selects which benchmark fund to use (of course, the benchmark you use should be appropriate for your asset allocation):


Missing data warning is referring to the inflation data for December, which won't be published until mid-January. 0% is used as a placeholder.

Here is the comparison of portfolio balance with the benchmark:


The Returns sheet also shows the performance of the benchmark fund :


Other tables and charts on the Balances and Returns sheets allow you to look at portfolio or benchmark data.

[BTW, there's currently a bug when the benchmark fund has less history than the portfolio. I'll try to track that down in the next week or so and post a bug fix.]

[9z6q4zd5]

Great timing for beginning of the year planning.

I did a manual copy/paste migration from V7. Only have a few accounts.

No issues to report.

Thank you for all your work on this project.

[scoothome]

Version 10.1 has been released.

Main Google Sheet with only one account populated (less data to clear when entering your own data).
Demo Google Sheet with multiple accounts populated to illustrate how it works.
Main Excel workbook with one account populated.
Demo Excel workbook with multiple accounts populated to illustrate how it works.

• Bug fix for when benchmark fund starts later than portoflio.
• Added another button in the Google Sheets version for copying benchmark data, but you won't need to use it unless you've modified the benchmarks data. (And it's still faster to copy/paste manually for GS; Excel macro works great.)

Bug fix corrects comparison of portfolio to benchmark fund when the benchmark fund starts later than the portfolio (Darn, just noticed I need to fix the since date in the chart title. I'll do that in-line later).

[scoothome]

With apologies, I was a bit hasty in publishing 10.1 and overlooked a number of necessary bug fixes to deal with cases where the benchmark fund has a later start date than the portfolio. Version 10.2 hopefully corrects everything. The changes were on multiple sheets, so I thought it best to issue a new release rather than trying to do in-line changes.

Version 10.2 has been released.

Main Google Sheet with only one account populated (less data to clear when entering your own data).
Demo Google Sheet with multiple accounts populated to illustrate how it works.
Main Excel workbook with one account populated.
Demo Excel workbook with multiple accounts populated to illustrate how it works.

[scoothome]

Version 10.2 has been released.

Main Google Sheet with only one account populated (less data to clear when entering your own data).
Demo Google Sheet with multiple accounts populated to illustrate how it works.
Main Excel workbook with one account populated.
Demo Excel workbook with multiple accounts populated to illustrate how it works.

FYI, I made in-line updates to change the drop-down menu for selecting Portfolio or Benchmark on the Balances sheet (cell A63) and Returns sheet (cell A33). Rather than listing the specific benchmark fund in the drop-down, it just says "Benchmark". This prevents getting a validation error in those cells (if they are not set to "Portfolio") when selecting a different benchmark fund in cell A2 on the Balances sheet. I added some formulas to the respective helper sheets and point chart title references to those cells so they would still show the specific benchmark fund.

Also, for the DEMO files I've manually hidden rows on the Balances and Returns sheets for which there are no data to make it more compact. I'll add a way to do this with the click of a button in the next major release.

[scoothome]

Version 11.0 has been released.

Main Google Sheet with only one account populated (less data to clear when entering your own data).
Demo Google Sheet with multiple accounts populated to illustrate how it works.
Main Excel workbook with one account populated.
Demo Excel workbook with multiple accounts populated to illustrate how it works.

• Added button to hide/unhide blank data rows on Balances and Returns sheets
• Modified summary display of initial balance and cumulative cashflows for accounts that were opened later than portfolio start date
• Added percentage and dollar drawdown charts for portfolio and accounts
• Cleaned up chart titles by using dynamic text for entire title rather than bits and pieces of it
• Edit: forgot to mention bug fix for Google Sheets, Growth of $10k account chart (account series weren't as labeled)

Button to hide/unhide rows is on the Balances sheet (cell G2). Clicking the button will hide blank data rows on both Balances and Returns sheets. Clicking the button again will unhide. For Excel, I was able to change the text and color of the button when pushed. For Google Sheets, from a quick internet search, it doesn't appear I can change the drawing object properties via script. Instead, the instruction text next to the button is changed (including color).


Modified summary initial balance and cumulative cashflows for accounts that were opened later than portfolio start date. Now the initial balance reflects the amount deposited into the account in the first month.


Added percentage and dollar drawdown charts. Here are the charts for the portfolio, for the actual portfolio balances and cashflows (MWRR).
Percentage drawdown:


Dollar drawdown (note that cashflows more than offset market losses in the earlier years):


You can also plot $10k initial balance with no cashflows (TWRR). Here are the drawdown charts for VFINX (S&P 500 Index fund).
Percentage drawdown:


Dollar drawdown:

[scoothome]

Version 11.0 has been released.

FYI, I made an in-line update to the Excel version to add a minus sign in front of the dollar drawdown y-axis labels, specifically. For some reason, I didn't need to do that for the percentage drawdown y-axis labels (although initially is wasn't showing minus for % either...). No issues for Google Sheets custom formatting on the drawdown chart.

[scoothome]

Here's a preview of version 12, which will be released by the end of weekend. This version adds a section to sheet MyPortfolio for you to define custom benchmarks using the funds on the Benchmarks sheet. You can also specify how often to rebalance the custom benchmarks.



Note to Google Sheet users: I mistakenly made a change to the % and $ drawdown charts in version 11 which resulted in the recovery lines being less vertical. This has been corrected in-line and it's easy to update if you're already using version 11. Just edit the charts and uncheck the box at the bottom of the setup tab that says, "Treat labels as text".

[scoothome]

Version 12.0 has been released.

Main Google Sheet with only one account populated (less data to clear when entering your own data).
Demo Google Sheet with multiple accounts populated to illustrate how it works.
Main Excel workbook with one account populated.
Demo Excel workbook with multiple accounts populated to illustrate how it works.

• Added ability to define custom benchmarks and specify rebalancing frequency
• Google Sheets: merged script to copy benchmarks into Copy MyPortfolio script

Added section to MyPortfolio sheet (N15:S24) to define custom benchmarks using funds on Benchmarks sheet. This image is from the Google Sheets version, the Excel version is similar but has only one button.


User can select desired rebalancing frequency for the custom benchmarks.


The custom benchmarks will appear in the "select portfolio benchmark" dropdown menu on the Balances sheet.


Charts on both Balances and Returns sheets will show a visual reminder of the rebalancing frequency if a custom benchmark has been selected.


Edit: For Excel users, I've noticed that after running the HideRows macro on the Balances sheet the text gets fuzzy. Clicking to another sheet and then back to the Balances sheet clears things up. For GS users, the scripts take much longer to run than Excel macros. For example, the HideRows script for GS takes about 18 seconds to complete whereas Excel finishes in 2 seconds. I'm not sure why that is.

[scoothome]

Here's a preview of Version 13, which will be released next weekend after incorporating January data.

Moved portfolio benchmarks rebalancing selection from MyPortfolio sheet to Balances sheet.


When a single mutual fund is selected as the benchmark, the rebalancing selection is greyed out.


Added portfolio and account returns histograms to complement the annualized trailing returns charts. Here's the existing annualized trailing returns chart for the benchmark set to VFINX, real returns, TWRR, rolling 10 year periods in the Google Sheets version.


Here's the corresponding histogram chart.


The implementation is slightly different for Excel (makes use of secondary y-axis). Here's how it looks in the Excel version.

[scoothome]

Version 13.0 has been released.

Main Google Sheet with only one account populated (less data to clear when entering your own data).
Demo Google Sheet with multiple accounts populated to illustrate how it works.
Main Excel workbook with one account populated.
Demo Excel workbook with multiple accounts populated to illustrate how it works.

• Moved rebalancing selection from MyPortfolio sheet to Balances sheet.
• Simplified rebalancing on hidden sheet PortfolioBM.
• Added returns histogram charts for Portfolio / Benchmark and Account.
• Added VFMXX (Vanguard Federal Money Market Investor) and VUSXX (Vanguard Treasury Money Market Investor) to Benchmarks sheet.

See screenshots from previous post.

Tip for existing users: populate your current PIRS file with January data before updating to v13. For your Benchmarks sheet, you can copy data from v13, if applicable. However, you will first need to run the script to insert a row on the Benchmarks sheet in your current file (do not manually insert a row or it will mess up the formula references on the hidden BenchmarksHelper sheet). When you have no missing data (apart from January inflation data, which won't be released until mid-February), then run the scripts on your copy of the v13 file to transfer data from your up-to-date current file. If you want to make use of VMFXX or VUSXX for benchmarks, I recommend copying them to your current file before updating to v13.

FYI, the ongoing maintenance actions are:
1. Update Account sheets with latest data
2. Update Benchmarks sheet with latest data (I will be updating benchmark data monthly so if you keep those funds in your file, you can copy latest data from the relevant file linked in OP)
3. Update Inflation sheet with latest data (typically available on Bureau of Labor Statistics website mid-month for the previous month)

For the Excel version the histogram charts use a secondary axis. I didn't see a way to automatically make the secondary y-axis scaling equal to the primary y-axis, so there's a bit of a hack under the hood that tries to do just that. It works for all data I've tested so far, but there might be an edge case or two where the two axes don't have the same min/max scaling.

For the Google Sheets version the histogram charts use only a primary y-axis. I couldn't figure out how to add data labels for -StDev, Avg, +StDev that dynamically update, but if you click on the chart and hover the cursor on the data point it will show the value. GS continues to be much slower than Excel, especially when running scripts and less so when changing input selections.

[scoothome]

FYI, the Bureau of Labor Statistics released CPI data for January. Update your copy of PIRS by entering 317.671 in cell B97 on sheet Inflation.

[Shred00]

Any chance anyone can confirm having this workbook working on LibreOffice (or OpenOffice I suppose)?

I'm pretty green with the whole macro business in spreadsheets and just want to get confirmation from anyone that they work in *Office before I waste a bunch of time trying to figure this macro stuff out there.

[scoothome]

Any chance anyone can confirm having this workbook working on LibreOffice (or OpenOffice I suppose)?

I'm pretty green with the whole macro business in spreadsheets and just want to get confirmation from anyone that they work in *Office before I waste a bunch of time trying to figure this macro stuff out there.

I haven't tried, but I doubt porting to LibreOffice or OpenOffice will work unless they natively support functions FILTER, VSTACK, HSTACK, etc.

[Shred00]

Is it possible to use benchmark data that is in open/close/high/low/etc. such as https://www.morningstar.com/etfs/xtse/xbal/chart?

Or does anyone know how to make Morningstar provide the NAV/NAV Growth format for XBAL?

I guess not. Noting the README sheet does say limited to mutual funds for now.

[scoothome]

Is it possible to use benchmark data that is in open/close/high/low/etc. such as https://www.morningstar.com/etfs/xtse/xbal/chart?

Or does anyone know how to make Morningstar provide the NAV/NAV Growth format for XBAL?

I guess not. Noting the README sheet does say limited to mutual funds for now.

While not a perfect match, using VSMGX or a custom benchmark of 60% VTWIX, 40% VBMFX gets you pretty close.
https://www.portfoliovisualizer.com/bac ... TcidE6XaEC

[Shred00]

I haven't tried, but I doubt porting to LibreOffice or OpenOffice will work unless they natively support functions FILTER, VSTACK, HSTACK, etc.

Looks like maybe those will come with LibreOffice version 25.8 according to https://wiki.documentfoundation.org/Fea ... soft_Excel and https://wiki.documentfoundation.org/ReleaseNotes/25.8.

Thanks for the insight.

[Shred00]

How should one handle adding an account after the start of the portfolio. I.e. if I set MyPortfolio start to 2024/1 and then open a new account in March/2024 what should I do in that account's sheet? Do I set the values before March to 0 or leave them blank or something else?

I tried setting them empty but then the Returns sheet had some MWRR problems with it.

[Shred00]

While not a perfect match, using VSMGX or a custom benchmark of 60% VTWIX, 40% VBMFX gets you pretty close.

I tried the latter. (I'd post a screenshot just to demonstrate how I added it but I don't see the ability to add attachments here.)

In any case, that benchmark results in a TWR for 2024 of 10.3% (in cell D141) but https://www.blackrock.com/ca/investors/ ... liotm-fund shows 1y returns are 16.13% as of Dec. 31, 2024. Am I misunderstanding something about all of this? Is the discrepancy due to accounting for DRIPs or something? I'd be surprised if large discrepancy is simply due to re-investing dividends. It seems to be to be just that.

[scoothome]

How should one handle adding an account after the start of the portfolio. I.e. if I set MyPortfolio start to 2024/1 and then open a new account in March/2024 what should I do in that account's sheet? Do I set the values before March to 0 or leave them blank or something else?

I tried setting them empty but then the Returns sheet had some MWRR problems with it.

Look at the DEMO file. The portfolio starts in January 1995, but account "HisRothIRA" starts in July 2003. On that account sheet, columns C, D, and E have zeros populated until July 2023.

[scoothome]

While not a perfect match, using VSMGX or a custom benchmark of 60% VTWIX, 40% VBMFX gets you pretty close.

I tried the latter. (I'd post a screenshot just to demonstrate how I added it but I don't see the ability to add attachments here.)

In any case, that benchmark results in a TWR for 2024 of 10.3% (in cell D141) but https://www.blackrock.com/ca/investors/ ... liotm-fund shows 1y returns are 16.13% as of Dec. 31, 2024. Am I misunderstanding something about all of this? Is the discrepancy due to accounting for DRIPs or something? I'd be surprised if large discrepancy is simply due to re-investing dividends. It seems to be to be just that.

They have different portfolio compositions (e.g., XBAL has 44% of holdings in Canada, much higher than VSMGX), so it's not surprising their performance differs.

[Shred00]

Look at the DEMO file. The portfolio starts in January 1995, but account "HisRothIRA" starts in July 2003. On that account sheet, columns C, D, and E have zeros populated until July 2023.

That's exactly what I did. The only difference that I can see is that for the DEMO file, the account exists for more than a year after it starts but in my case, the portfolio starts in 2024/01 but the account doesn't start until March 2024. Maybe there is an issue with MWRR when there is less than a year's worth of data, or something along those lines?

They have different portfolio compositions (e.g., XBAL has 44% of holdings in Canada, much higher than VSMGX), so it's not surprising their performance differs.

Maybe it's of little consequence (a difference without a distinction sort of thing) but rather than using VSMGX I went with your suggestion of 60% VTWIX, 40% VBMFX. I was hoping either/both would be similar enough to XBAL to get at least near the same return for 2024.

I was able to coax the $10K growth data out of Morningstar for this ETF using https://www.morningstar.ca/ca/report/et ... 0P0000934G and then using Show Interactive Chart FWIW.

[scoothome]

That's exactly what I did. The only difference that I can see is that for the DEMO file, the account exists for more than a year after it starts but in my case, the portfolio starts in 2024/01 but the account doesn't start until March 2024. Maybe there is an issue with MWRR when there is less than a year's worth of data, or something along those lines?

The MWRR formula should calculate returns for less than 1 year, as shown in columns AG:AJ of the account sheets in the DEMO file. If you want to send me a PM with a link to your spreadsheet, I'll try to take a look this weekend.

[Shred00]

The MWRR formula should calculate returns for less than 1 year, as shown in columns AG:AJ of the account sheets in the DEMO file.

I see my problem. I added the final account values without adding the initial contribution (which in real life of course is needed to get the account to actually have a final value).

Thanks for your guidance in helping me figure out what my issue was.

[Shred00]

Is there any way to see the entire workbook as of a certain date?

Let's say that I have added all of the values to the various accounts sheets as of the end of January (and it will be one more row in the accounts sheets added as of the end of February in a couple of days even), but I want the Returns and Balances (etc.) sheets to evaluate only my performance as of the end of 2024 so that 3m/6m/YTD numbers etc. all reflect the first quarter/half and full year of 2024 without including the values that have been added for 2024.

Is this possible in any way?

Update 1: Looking through the various sheets, I see a lot of references to TODAY(). Would it be possible to replace all of those with a reference to a cell that has an as of date in it, which defaults to TODAY()? Would that achieve the desired goal?

Update 2: Yes, replacing TODAY() across all sheets with a reference to a cell with a date in it seems to work. Is that something we can get added to the PIRS workbook(s)?

[scoothome]

Is there any way to see the entire workbook as of a certain date?

The TODAY function is only used to provide prompts for when you should add data. To achieve what you are asking, don't mess with that and instead modify the cell references on the hidden sheet "ReturnsHelper", starting in row 14. The formulas in that section refer to the last non-blank row, which is shown in row 10. Instead of the last non-blank row, you want to replace that row number with the row number of the last December in the data set. I can give more detail on how to do it, but not until the weekend.

Edited to add: yes, I see what you are looking for and can add an as of date cell. I’d want to do some testing to make sure it doesn’t break anything.

[scoothome]

Version 14.0 has been released.

Main Google Sheet with only one account populated (less data to clear when entering your own data).
Demo Google Sheet with multiple accounts populated to illustrate how it works.
Main Excel workbook with one account populated.
Demo Excel workbook with multiple accounts populated to illustrate how it works.

• Added ability to show balances and returns as of specified date (default is end of previous month)
• Google Sheets version: changed "Copy Next 5 Accounts" to 4 because of issues exceeding maximum script time (6 minutes max for single script execution)

Tips for existing PIRS users:
1. Make a copy of PIRS 14.0
2. Update your current PIRS spreadsheet (i.e., v13.0 or earlier) with latest account and benchmark data before updating! For benchmark data, can copy from v14.0 "Benchmarks" tab if using same funds. Otherwise grab the latest data from Morningstar (see README).
3. Run the scripts in your v14.0 copy to transfer data from the previous version (or manually copy; Google Sheets users see note below)

Can specify date on sheet "MyPortfolio" cell B28. Don't enter an arbitrary date, use the last day of a month. The default is the last day of the previous month.


As an example, for the main file overwriting the formula in cell B28 with 12/31/2024, selecting VFINX as the benchmark on the "Balances" tab, then switching to the "Returns" tab:


You can ignore the warning about missing/extraneous data (data entries for January and February are not used in the calculations, but result in extraneous data warnings).

Note to Google Sheets users: the scripts to transfer data are frustratingly slow. The total time to transfer data from the previous PIRS DEMO file took 19 minutes compared to less than 1 minute for the Excel version. It's quicker to manually copy the data. If you do use the buttons, note that you won't get a script status banner after clicking the "Copy MyPortfolio" button because it first asks to authorize the script, and then doesn't display the script status banner after the authorization has been completed. However, I do use Toast Alerts that pop up in the lower right of the Google Sheet as the script is executing. Wait until the "Copy MyPortfolio" button and instruction text disappears before proceeding to the "Copy Next 4 Accounts" button.

[Shred00]

Does the Copy MyPortfolio button literally just copy the contents of the accounts sheets, in the yellow cells, to the new version? So if I had formulas, references to other cells, etc. in those yellow cells are those are copied as is (i.e. preserving formulas, cell references, etc.)? If so, how many rows are copied? Just until the end of the most recent month, or if I had filled the cells of those three columns down to the end of the sheet, are those all copied?

What are your thoughts on a sheet or maybe a portion of the MyPortfolio sheet that contains extra miscellaneous user data that is copied during these Copy MyPortfolio operations? Specifically, for example, I want a cell that I can write the URL of a sheet that I want to reference in other (i.e. Account) sheet IMPORTRANGE() formulas as I have another sheet that I track monthly (and other) account balances and not having to manually transpose those would be helpful.

Also, would it be too much trouble to add a script/function for:

Code: Select all

function SHEET() {
  return SpreadsheetApp.getActiveSheet().getIndex();
}

[scoothome]

Does the Copy MyPortfolio button literally just copy the contents of the accounts sheets, in the yellow cells, to the new version? So if I had formulas, references to other cells, etc. in those yellow cells are those are copied as is (i.e. preserving formulas, cell references, etc.)? If so, how many rows are copied? Just until the end of the most recent month, or if I had filled the cells of those three columns down to the end of the sheet, are those all copied?

What are your thoughts on a sheet or maybe a portion of the MyPortfolio sheet that contains extra miscellaneous user data that is copied during these Copy MyPortfolio operations? Specifically, for example, I want a cell that I can write the URL of a sheet that I want to reference in other (i.e. Account) sheet IMPORTRANGE() formulas as I have another sheet that I track monthly (and other) account balances and not having to manually transpose those would be helpful.

GS users are welcome to view the scripts by clicking Extensions in the menu, then selecting Apps Script. The Copy MyPortfolio button (script copyMyPortfolioData.gs) only copies data from the Benchmarks tab and the yellow cells of the MyPortfolio sheet of the file specified in the UI prompt Get Previous PIRS File ID. Account data are copied using the Copy Next 4 Accounts button (script copyAccountData.gs). Note that only values are copied (for the entire yellow block C13:E997 and G9:G10), not formulas. Users can modify the script as desired to copy what's needed for their intended use. The internet is your friend here, that's generally how I stumbled on how to code the functionality I was seeking.

I could potentially add miscellaneous user data in the copy scripts, but honestly it would be faster to manually copy/paste.

For Excel users, the scripts can be accessed by clicking Developer in the menu, then clicking Macros. For whatever reason, the Excel macros are much, much faster than Google Apps script.

[Shred00]

Users can modify the script as desired to copy what's needed for their intended use. The internet is your friend here, that's generally how I stumbled on how to code the functionality I was seeking.

Understood. If a user were to come up with such an enhancement and were to post the script changes (or just the updated workbook) back here, would you be amenable to updating the master PIRS with those enhancements?

I wonder what your thoughts are on supporting more than 20 accounts? Given that an individual and a spouse could have as many as 4 to 6 (i.e. different kinds of) accounts each at a brokerage just one or two brokerage switches would wind up with more than 20 accounts assuming one wanted to continue to keep historical performance data.

Again, I'm willing to do the work of making the enhancements and provide them (or the workbook) back. It would just be easier if the canonical PIRS releases had the functionality built-in so that I didn't have to patch it into my own personal version on every new PIRS release. With a bit of effort, I think it can be done so that the canonical PIRS still only has 20 accounts defined as a default but the update (i.e. from one release to another) process could extend the 20 if it found the source has more than 20 defined.

[scoothome]

Understood. If a user were to come up with such an enhancement and were to post the script changes (or just the updated workbook) back here, would you be amenable to updating the master PIRS with those enhancements?

Yes, PIRS users are welcome to post suggested script additions/changes or a link to a modified workbook (preferred if there are many changes) that incorporates new features. If enough users express interest in the enhancements I am happy to fold these into the PIRS master. However, there is a tradeoff between adding new features and degraded performance. PIRS is already bloated and I have some concerns that adding more features that will benefit some users may make detract from the experience of others.

Note that adding more accounts is not trivial. Adding more account sheets is easy, but changes would be required on multiple sheets (including hidden ones). How many account sheets are you looking for?

Also, a reminder that my time is generally limited to working this occasionally on weekends. I may be slow to fold in suggested enhancements, especially since I'd want to do sufficient testing to make sure new features don't break anything.

[Shred00]

Note that adding more accounts is not trivial. Adding more account sheets is easy, but changes would be required on multiple sheets (including hidden ones). How many account sheets are you looking for?

Indeed, I do get the feeling that adding more accounts is not trivial which is why I'm hoping that adding the ability to allow them to be increased (while maintaining a default, such as the 20 you provide) is something that will be acceptable for the canonical PIRS spreadsheet as I'd hate to have to keep patching the changes in on every release.

Some hard-coded range copying during update will have to be made more dynamic for one thing. But ultimately, I am hopeful that those that want to maintain the 20 or less will not see any adverse impact and those that need more than 20 will of course see performance (hopefully) reasonably commensurate with the additional data they are able to maintain.

As for how many I am looking for, I think it would be on the order of maybe 26-27, as we have had to switch brokerages a few times in the past few years (moving 8-9 accounts each time) when we have found the existing one to be inadequate, usually due to significant changes in price structures or declines in service, quality and accuracy, etc. as brokerage pricing seems to be racing to the bottom.

[scoothome]

Note that adding more accounts is not trivial. Adding more account sheets is easy, but changes would be required on multiple sheets (including hidden ones). How many account sheets are you looking for?

As for how many I am looking for, I think it would be on the order of maybe 26-27, as we have had to switch brokerages a few times in the past few years (moving 8-9 accounts each time)

You should not think of a PIRS account as being tied to a particular brokerage. You really only have 8-9 accounts from a PIRS perspective. It doesn’t matter that a “HisRothIRA” has changed brokerages. It’s the same PIRS account and you just continue to update monthly cash flow in and out, and end of month balance. In my opinion, there’s no need to create a separate PIRS account for this scenario (moving to a different brokerage).

[Shred00]

You should not think of a PIRS account as being tied to a particular brokerage. You really only have 8-9 accounts from a PIRS perspective.

I did consider that. But then I also considered the ease of just transposing numbers into sheet cells vs. having to add numbers across similar accounts if you have multiple brokerages at the same time. This is a case where copying formulas from accounts sheets when migrating to a new version would be nice as I could just enter =SUM()s of the account values at the different brokerages to keep things a little more transparent (i.e. how the final value was arrived at).

So yeah, I'm still on the fence about which mechanism I like better.

[scoothome]

I am offering PIRS users an opportunity to contribute to adding/maintaining benchmark funds. This involves volunteering to update PIRS benchmark fund data on a monthly basis. All PIRS users would then have the ability to copy/paste benchmark funds to their PIRS file (Google Sheets or Excel) for more custom benchmark possibilities.

The file is located here: PIRS_Benchmarks

1. PM me with Gmail ID if interested in adding and then maintaining PIRS benchmark data. I will give you edit access to the spreadsheet.
2. Volunteers agree to do monthly updates for funds they have added.

On Benchmarks sheet, users can append fund data.


On LOG sheet, users add info about which funds they will update on a monthly basis.

[Shred00]

I am offering PIRS users an opportunity to contribute to adding/maintaining benchmark funds. This involves volunteering to update PIRS benchmark fund data on a monthly basis. All PIRS users would then have the ability to copy/paste benchmark funds to their PIRS file (Google Sheets or Excel) for more custom benchmark possibilities.

This is interesting. I'm not sure what capabilities Excel has for this, but Google Sheets has IMPORTRANGE(). Could the canonical Google Sheets PIRS utiilze that function for pulling the benchmark data so that monthly copy and paste by everyone could be avoided? If you are amenable, I will see about making that modification and posting you back an updated PIRS for your review and publication.

[scoothome]

This is interesting. I'm not sure what capabilities Excel has for this, but Google Sheets has IMPORTRANGE(). Could the canonical Google Sheets PIRS utiilze that function for pulling the benchmark data so that monthly copy and paste by everyone could be avoided? If you are amenable, I will see about making that modification and posting you back an updated PIRS for your review and publication.

My preference would be to screen scrape from Morningstar to automate the benchmark fund update, but I couldn't figure out how to do it. If that can be done, then we won't really need this standalone PIRS_Benchmarks workbook. Lacking that, an automated import from this workbook would be nice to have.

[Shred00]

My preference would be to screen scrape from Morningstar to automate the benchmark fund update, but I couldn't figure out how to do it. If that can be done, then we won't really need this standalone PIRS_Benchmarks workbook. Lacking that, an automated import from this workbook would be nice to have.

Yessir, indeed. Screen-scraping (or otherwise automatically importing) occurred to me the moment that I first saw that sheet too and would also be my preference. But a good runner-up would be using IMPORTRANGE() IMO, at least so that everyone gets to leverage off of the small effort of the few maintainers. Maybe that carrot will help encourage people help maintain the one source of information so that a a reference in the PIRS sheet is useful.

Anyway, I've added XBAL to the benchmarks sheet and will endeavour to maintain it monthly as I do my monthly performance tracking work.

Thanks for initiating this effort.

[retiringwhen]

My preference would be to screen scrape from Morningstar to automate the benchmark fund update, but I couldn't figure out how to do it. If that can be done, then we won't really need this standalone PIRS_Benchmarks workbook. Lacking that, an automated import from this workbook would be nice to have.

M* has been pretty aggressive in making their site unscrapable for obvious business reasons.

Sadly, Vanguard is too, for much less obvious reasons.

[scoothome]

FYI, I noticed a bug that resulted in #NUM errors on sheet Missing when I updated my personal copy (alas, my personal portfolio is not the $4.2M DEMO portfolio. I wish!). I made an inline update to the formula in cell B8 on sheet Missing for the main Excel and GS files as follows:

Code: Select all

=IF(MyPortfolio!$B$28=Benchmarks!$A$7,"",IF(MyPortfolio!$B$28>Benchmarks!$A$7,DATEDIF(Benchmarks!$A$7+1,MyPortfolio!$B$28+1,"M"),DATEDIF(MyPortfolio!$B$28+1,Benchmarks!$A$7+1,"M")))

[scoothome]

The U.S. Bureau of Labor Statistics has released inflation data for February. Update your PIRS by entering 319.082 in cell C97 on sheet Inflation.