07/18/14 - Version 3.1.30 (V3.1.29 skipped)
- Added code to the Lookup page to detect duplicate Asset Category Type codes in Column R. Thanks to Tim Caban of Copper Beech Advisors LLC for identifying this issue.
- Added code to import Risk page radio button selections from a source pyramid (EMWB-to-EMWB pyramids only). Thanks to Bridget Sullivan-Mermel of Sullivan/Mermal Inc. for this suggestion.
07/09/14 - Version 3.1.28 (V3.1.27 skipped)
- Fixed a small problem on the Investment Portfolio page that may have prevented users from changing the state of the 'Cross-check' checkbox
- Completely rewrote the 'Paynter-to-Paynter' import code - it is now 3-4 times faster, and more accommodating for import across networks.
- Added code to the Allocation page to highlight differences between the 'Beg/End Balance' and 'Txfr I-O/Chk' amounts. Now if the 'Chk' amount is less than the Beg/End Balance amount, it is shown in green. If it is greater it is displayed in red. If it is equal it is shown in black. Thanks to Jo Anne Paynter of Partnership Financial for this suggestion.
05/15/14 - Version 3.1.26
- On the Net Worth page, the restriction that A1 and A2 entries be positive has been removed. This means that artificially negative Inventory entries like "Overdraft Protection" (coded with a custom Type like 'OVR' with a 'Yes' in the 'Show on IP Tab' Lookup page column) will show up with the value in red on the Net Worth page. Thanks to Mellissa Bunton of Delphinus Financial Advisors for this suggestion.
02/08/14 - Version 3.1.25
- On the Net Worth page, category 'C1' was moved from 'Other Assets' to 'Real Estate'. Thanks to Melissa Bunton of Delphinus Financial Advisors for bringing this to my attention.
- Fixed a cell formatting bug in the upper allocations table on the Allocations page. The formatting for the cell in the 'Trade totals by type' row was not being properly copied to new Asset Class columns. Thanks to Timothy Caban of Copper Beech Advisors LLC for pointing this out.
01/23/14 - Version 3.1.24
- Now only considers 'Type' codes with a 'Yes' in the 'Show On IP Tab' Lookup page column when generating the lower matrix on the Allocation page. Previously was hard-coded to skip 'P' and 'RE' only, but accept everything else, which worked fine until my wife added the 'OB' (Owner Business) and 'COL' (Collectible) custom types ;-). Thanks (I think) to Jo Anne Paynter of Partnership Financial for bringing this to my attention.
- Now copies all 'Planner Notes' rows on the Client Info page, instead of terminating at the first blank 'date' cell. Thanks to Rob Reed of Partnership Financial for identifying this problem.
01/20/14 - Version 3.1.23
- Re-instated the code that copies pie chart colors from the source pyramid - this was inadvertently left out starting at 3.1.19. Thanks to Melissa Bunton of Delphinus Financial Advisors for pointing this out
- Adjusted pie chart sizes on the Investment Portfolio page, and changed the print orientation of this page from landscape to portrait, to allow 1-page printing. Thanks to Rob Reed of Partnership Financial LLC for bringing this to my attention.
- A few other minor aesthetic changes and bugfixes
01/13/14 - Version 3.1.22
- Fixed a problem that prevented a user from adding a custom asset category line to Lookup table columns T-V. Thanks to Michelle Morris of Brio Financial Planning for bringing this to my attention.
12/27/13 - Version 3.1.21
- Fixed typos that caused some Lookup page columns to be skipped on import. Thanks to Melissa Bunton of Delphinus Financial Advisors for pointing this out.
12/26/13 - Version 3.1.20
- Fixed a minor bug that caused an error message to appear after an import. Thanks to Melissa Bunton of Delphinus Financial Advisors for bringing this to my attention.
- Undid some of the Lookup table speedup code, as it incorrectly assumed that the source and destination pyramids have the same number of occupied rows. Still much faster though ;-).
12/25/13 - Version 3.1.19
- Speeded up EMWB-to-EMWB Import by a factor of 3 or so. A typical 50-line import now takes less than 3 seconds.
12/23/13 - Version 3.1.18
- Improved discrimination between 'New' (2007) and 'EMWB' (Paynter) pyramids for import. Thanks to Mellisa Bunton of Delphinus Financial Advisors for bringing this to my attention.
- Changed 'Account Name' and 'Account Number' Lookup column headers to 'User Def 1' and 'User Def 2' and made them user-editable.
- Added 'User Def 3" and "User Def 3" columns to Lookup table. Thanks to Rob Reed of Partnership Financial for this suggestion.
- Added status bar progress reporting for 'New' pyramid imports
- Fixed problem where Sub-type and Index column data wasn't being copied to destination pyramid. Thanks to Mellisa Bunton of Delphinus Financial Advisors for bringing this to my attention.
- Fixed problem that caused a custom ownership code to be converted to 'U' (Unknown) during import. Thanks to Mellisa Bunton of Delphinus Financial Advisors for bringing this to my attention.
12/13/13 - Version 3.1.17
- Fixed a bug that caused CA/Type Cross Check to incorrectly highlight the first blank row at the bottom of the Inventory
- Fixed a bug that occasionally caused the CA/Type Cross-Chk button to get out of synch
- Added progress messages to the status bar during Import operations
- When many rows are added to the Investment Portfolio page 'Detail View', the 'Detail' Pie charts can be pushed off below the visible screen due to Excel's automatic page breaks. I have attempted to minimize this problem by reducing the chart size as detail rows are added
- Fixed a bug that caused both 'FAA Summary View' and 'Detail View' pie charts to obscure the data tables
12/05/13 - Version 3.1.16
- Inventory Import now skips Lookup Column X (CA Category) as it is not planner-editable, and the new '**' comment symbol was getting overwritten with a blank cell.
- Fixed a bug that caused incorrect behavior on the Client Info page if the NWH table was empty and one of the new checkboxes was clicked
- Fixed a bug on the Client Info page that prevented new NWH lines from being immediately visible in the NWH chart
- Some minor changes on the Inventory page to speed up Import
12/03/13 - Version 3.1.15beta
- Finally figured out how to support copying pie chart color selections from one pyramid to another. Thanks to Anne McFarland of Davis Financial Planning for getting me off my butt on this one!
- Removed second signature block from Trades page. This was put in earlier, but there was significant 'pushback' on this feature so I removed it again.
- Fixed a bug on the Inventory page that caused the 'CA/Type Cross-chk' button label to toggle even when there was nothing to do.
- More speedup work on the Ownership page
11/30/13 - Version 3.1.14beta
- Fixed a problem with pie charts on Investment Portfolio page - the new pie chart arrangement was getting its data from the percentage columns instead of the dollar value columns, which made the displayed percentages incorrect
- Added code to speed up processing on the Investment Portfolio page. Processing time was reduced from about 2 seconds to about .3 seconds - a noticeable change.
11/29/13 - Version 3.1.13beta
- Fixed a problem in the new code to update Investment Portfolio pie charts that prevented several other pages from updating properly. Thanks to Michelle Morris of Brio Financial for pointing this out.
11/27/13 - Version 3.1.12beta
- Fixed a bug that caused import of older (pre 2.2.51) pyramids to not import IP detail rows properly. Thanks to Susan Honig of Veritana Financial Planning for bringing this to my attention
- Added code to properly regenerate IP detail section pie charts. Thanks to Susan Honig of Veritana Financial Planning for bringing this to my attention
- Added code to check IP chart percentages each time the Investment Portfolio page is opened
- Added code to skip unnecessary protection checks to speed up the Inventory page
11/26/13 - Version 3.1.10beta
- Replaced the code that makes sure the Investment Portfolio page is displayed properly - this was commented out for debugging purposes and not uncommented - Thanks to John Dulmage for pointing this out.
11/24/13 - Version 3.1.09beta
- Explicitly set the SUM formula for all Investment Portfolio page detail section columns.
- Fixed a bug that prevented update of the IP page FAA summary total background color
- Thanks to John Dulmage for putting up with my 'beta issues' ;-).
11/22/13 - Version 3.1.06beta
- Fixed a bug on the Inverstment Portfolio page that incorrectly calculated the 'Current' percentages in the IE and EQ detail sections. Thanks to John Dulmage for pointing this out
11/22/13 - Version 3.1.05beta
- Revised the Inventory page to jump a user click in Row 4 (column header row) to Row 5 vice Row 3. Thanks to David Dowd for this suggestion.
- Added a second checkbox to the Inventory row in the 'Smart Print' dialog to extend the print area to the 'User Def' columns
- Added a second set of signature blocks to the 'Client View' area of the Trade sheet. Thanks to John Dulmage for this suggestion.
- Changed the Net Worth History chart to a 'stacked bar' type, with user-selectable columns and colors. Thanks to Jackie Kleinman for this suggestion
- Added an 'Update Date' button to the Risk page. Thanks to John Einberger for this suggestion
- Widened the 'Typ' column on the Allocation page, so 'MME' doesn't display as 'MM'. Thanks to Jo Anne Paynter and John Einberger for this suggestion.
11/07/13 - Version 3.03
- Fixed (again!) the problem where 'Sam and Janet Evening' shows up in the header and footer when the default Excel Print Preview feature is used. This seems to be the bug that just won't die :-(
- Put the '**' (double asterisk) back in the Lookup page CA Category column. This had apparently gotten dropped in V3.0.02.
11/03/13 - Version 3.0.02
- Revised Paynter Pyramid - Paynter Pyramid import code to properly synchronize the number of rows in the destination detail section of the Investment Portfolio page with the source detail section. It also updates the entire IP page during the import process so that a planner who does not view/activate the IP page after an import and instead goes directly to the Allocation page, will still get completely valid results. Thanks to Jo Anne Paynter of Partnership Financial for identifying this issue
- Removed the 'Could not find account number for..." message from the Trade Sheet coding. Thanks to John Dulmage of Financial Pathways for this idea.
10/26/13 - Version 3.0.01
- Reorganized and updated license management code to eliminate potential for future DLL name conflicts. Thanks to David Zolt of Westlake Advisors for identifying this potential issue.
- Fixed typos on Risk Management page and made a minor adjustment to chart size on the Investment Portfolio page to allow it to print on one page. Thanks to Ken Robinson of Practical Financial Planning for identifying these issues
- Added code to copy the column header labels for the 'User Defined' columns on the Inventory page, so that user customized header labels don't get lost during when importing into a blank pyramid. Also changed the background color of these cells to indicate user editability. Thanks to Michelle Morris of BRIO Financial Planning for identifying this issue.
- Added a "**" (double asterisk) CA Category code to allow planners to insert comment lines into the inventory without causing subsequent lines to be dropped from Sort or Smart Print operations. Note that even with the "**" CA Cat code, entries with non-zero "Current Value" cells WILL STILL BE INCLUDED in Investment Portfolio and/or Pyramid totals. Thanks to John Einberger of Mutual Advantage for suggesting this.
09/22/13 - Version 2.2.68
- Fixed a problem where hidden columns on the Inventory page would cause an error when the 'Get Allocation Data' button was pressed on the Allocation page. Now any hidden columns are detected and UNhidden before the Get Allocation Data function runs, and then they are re-hidden afterwards. Thanks to Jim Davis of Partnership Financial, LLC for identifying this problem.
09/10/13 - Version 2.2.67
- Hid the checkbox TRUE/FALSE cells on the pyramid page, per Jo Anne's request
- Put the new code for calculating the pyramid H40 value back in - inadvertently left out of V2.2.64 and subsequent (see V2.2.64 below for details)
- Unlocked the update date cell on the Risk Profile page - thanks to Greg Pierce of Financial Strategies for pointing this out.
09/03/13 - Version 2.2.66
- Fixed a bug that caused the checkbox states on the 'Smart Print' form to be remembered incorrectly.
- Fixed a bug that sometimes caused incorrect 'Smart Print' selection behavior.
08/30/13 - Version 2.2.65
- Fixed a bug that caused the 'deleted inventory formula' error message to be incorrectly displayed when the user first opened a blank pyramid. Thanks to John Dulmage of Financial Pathways for bringing this to my attention.
08/23/13 - Version 2.2.64 (Versions 2.2.62 and 2.2.63 skipped)
- Fixed a problem where negative C3 values were not being picked up in the Liabilities section on the Net Worth page. Now negative inventory items in A2, A3, B2, C1, C2, and C3 are all picked up properly. Thanks to John Discepoli of Discepoli Financial Planning. LLC for identifying this issue.
- Now any B2 inventory item for which the associated type code has a 'yes' in Lookup page column V is included when calculating the Real Estate Investment Portfolio value on the Pyramid page (cell H40). Thanks to Jacob Kuebler of Bluestem Financial Advisors, LLC for identifying the issue, and to Jo Anne Paynter of Partnership Financial for the cool solution.
- Added code to the Inventory page to automatically detect the situation where a planner has inadvertently deleted the formula from one or more of the hidden category summation columns (columns AA-AO), typically by inserting a row somewhere. Now the code pops a message box identifying the problem and suggesting a solution (importing the pyramid into a blank template usually solves the problem).
08/15/13 - Version 2.2.61
- Fixed a problem that caused the Inventory page Sort feature to fail if the user had inserted blank lines below the populated portion of the inventory. Thanks to Michelle Morris of BRIO Financial Planning for identifying this issue.
07/18/13 - Version 2.2.60
- Now only the header (Company Name) text attributes change when font and other text attribute changes are made to the 'Company Name' field on the Lookup page; Sheet title text attributes are no longer affected. Thanks to John Einberger of Mutual Advantage LLC for suggesting this
- The 'Smart Print' feature now includes the ability to select any available printer (including 'PDF printers') from the Smart Print dialog, independently of the Excel printer selection. Thanks to Jacob Kuebler of Bluestem Financial Advisors LLC for this suggestion.
- Fixed a bug that caused the user selections on the 'Smart Print' dialog to be remembered incorrectly.
07/15/13 - Version 2.2.59
- Fixed a bug that caused a 1004 run-time error on the Pyramid page. Thanks to Pam Webber of Partnership Financial LLC for reporting this.
07/13/13 - Version 2.2.58
- Fixed a bug that caused the user-added company logo on the Lookup page to be deleted. Thanks to John Dulmage of Financial Pathways for pointing this out so quickly!
07/13/13 - Version 2.2.57
- Corrected formatting problem on Real Estate Analysis page. Thanks to Rob Reed of Partnership Financial LLC for pointing this out
- Moved Inventory page scroll position back to row 1, column 1. Thanks to Wallace Larson of Essential Financial Strategies for bringing this to my attention.
- Slightly increased Pyramid page column B width to make NWH date drop-down list a bit more readable
07/04/13 - Version 2.2.56 (V2.2.55 skipped)
- Fixed a bug that could cause the Net Worth History on the Client page to not be updated properly after a change on the Inventory page, either from manual input or import from another pyramid. Thanks to Kyle Sheldon-Chandler of Lawrence Financial Planning for pointing this out.
- Added code to synchronize the sheet title and company name text on each page with the font attributes of the text in the Company Name field of the Lookup page. If the bold, italic, underline, or color attributes of the Company Name field text are changed, then the Company Name and Sheet Title text of all pages with a header are changed accordingly.
- Added code to synchronize the background fill color of the Company Name field with the currently selected fill color in the 'Header/Footer Color' field. This was done so the planner could easily preview the effect of different text and fill color combinations.
06/28/13 - Version 2.2.54
- Converted all the remaining Real Estate Analysis cell formulas that depended on Pyramid page cells so they only depend on Inventory page cells
06/28/13 - Version 2.2.53
- Fixed a bug that could cause the Real Estate Analysis page A1/A2 'Default' values to not be updated properly if it was opened before the Pyramid page. Now the Real Estate Analysis values are updated directly from the inventory and the Client page, independently of the Pyramid page. Thanks to Troy Thompson of Thompson Advisory Services for bringing this to our attention.
- Locked the Lookup table 'Life Cycle Stage' (Column A) and 'CA Category (Column X) values - these are not planner-editable. Thanks to Jo Anne Paynter of Partnership Financial LLC for pointing this out.
06/24/13 - Version 2.2.52
- Fixed a bug on the Investment Portfolio page. Cells I57 & I64 had gotten changed from SUM() formulas to fixed "100" values when I added the 'check IP against Pyramid' checkbox. Thanks to John Dulmage of Financial Pathways for pointing this out.
06/23/13 - Version 2.2.51:
- Added a 'Check against Pyramid IP Total' checkbox to the Investment Portfolio page, just under the 'Investment Portfolio Total' line. Checking this box will cause the IP total (F10) and Interest Earning Total (F8) values to be checked against the corresponding values on the Pyramid page. If they don't match, the offending cell is turned red. Thanks to Rob Reed of Partnership Financial LLC for the suggestion.
- Made the Employment Status (Column E) and FAA Category (Column N) cells non-editable, as these items are never changed within the ACA structure. The corresponding 'Liquidity Factor' and 'Default' values respectively are still planner-editable. Thanks to Jo Anne Paynter of Partnership Financial for working with me on these changes.
- Updated the 'User Notes' and 'Lookup Table Description' documents to include the above changes.
06/13/13 - Version 2.2.50:
- Corrected the 'Allowable CA Categories' column on the lookup page (Column T)
05/19/13 - Version 2.2.49:
- Added default values for 'Cash (A1)' and 'Emergency Cash (A2)' on the Real Estate Analysis page. These values now come from the third row of the A1 and A2 sections (cells D35 and F35 respectively) on the Pyramid page. Also changed the format of the 'Recom'd' cells (E16 and E17) on the Real Estate page from 'General' to 'Currency'. Thanks to Greg Pierce of Pierce Financial Strategies LLC for this suggestion.
05/09/13 - Version 2.2.48:
- Changed the default client name from 'Sam and Chanted Evening' to 'Sam and Janet Evening' per direction from 'she-who-must-be-obeyed' ;-).
- Added 'Employed/Self Employed' and 'Employed/Retired' employment status and liquidity percentages to the Lookup page, per Jo Anne Paynter of Partnership Financial
- Locked the remaining rows in the Employment Status column of the Lookup page to prevent any modifications to the list.
- Updated the 'ACA Financial Life Cycle' page using an image provided by R Wallace Larson of Essential Financial Strategies.
- Updated the 'Lookup Table Description' document
03/02/13 - Version 2.2.47: Made a number of changes to the Real Estate page
- Removed formulas from D16-D18 (Default) leaving them completely blank and unused
- Changed E16 & E17 background from white to salmon color, unlocked, and removed formulas - now advisor input
- Changed E15 text to 'Recom'd'
- Thanks to Wallace Larson of Essential Financial Strategies for continuing to nag me about inaccuracies and inconsistencies in the Pyramid, and to Jo Anne Paynter of Partnership Financial for once again bailing me out ;-).
02/25/13 Version 2.2.46:
- Corrected formula for Real Estate page Liquidity analysis default formula. Thanks to Wallace Larson of Essential Financial Strategies for pointing this out, and to John Einberger of Mutual Advantage for researching the issue.
- Revised error message for failed Inventory 'Import from File' operations. Thanks to Al Davis of Davis Financial Planning for bringing this to my attention
- Removed the '1 Retired/1 Employed' from Employment Status options
01/03/13 Version 2.2.45
- Removed the blank rows from the 'CA Category' drop-down list on the Inventory page.
12/04/12 Version 2.2.44
- Replaced the 2-page 'Financial Life Cycle' graphic with the more up-to-date one-page version provided courtesy of Bert Whitehead. Thanks to Jim Davis of Partnership Financial for the suggestion.
11/28/12 Version 2.2.43
- Adjusted the print range and page margins for the 'Trade Sheet' page so that both the trading notes and the trading details table are included in the printout. Thanks to Jo Anne Paynter of Partnership Financial for bringing this to my attention.
11/13/12 Version 2.2.42
- Fixed a bug on the Investment Portfolio page that prevented proper updates to the 'recommended dollars' column as changes are made to the 'recommended percent' column in the 'Investment Portfolio Detail' s and 'Functional Asset Allocation Summary' sections. This problem seems to occur only when a planner imports from a pyramid with zeroes in the 'recommended percent' fields. Thanks to Jackie Kleinman of KB Financial Advisory for bringing this to my attention.
- Fixed a missing formula in Row 5, Column N (current value) of the Inventory page. Thanks to Jo Anne Paynter of Partnership Financial for identifying this problem.
10/22/12 Version 2.2.41
- Made several modifications to the pyramid page layout and block colors in response to recommendations made during an 'Under the Trees' session at the recent ACA Update Conference.
- Changed E1, D2 & D1 colors from black to red (danger).
- Changed B1, A1, A2 colors from red to brown.
- Grayed out the 2nd and 3rd fields in the B1 block, and the 2nd field in the A1 and A2 blocks
- Moved the automatic 'Recommended A1' formula from D36 to D35 to avoid overwriting planner-entered value
- Moved the automatic 'Recommended A2' formula from F36 to F35 to avoid overwriting planner-entered value
10/01/12 Version 2.2.40
- Fixed a bug that caused the contents of the 'Allowable CA Category' column (column T) to be overwritten by blanks or garbage from older 'Paynter' pyramids. Now source information is copied only if the source cell contains a valid CA Category (A1, A2, .... E1). Thanks to Jo Anne Paynter of Partnership Financial LLC for identifying this issue.
09/30/12 Version 2.3.39 (2.2.37 & 2.2.38 skipped)
- Enhanced the operation of the 'Smart Print' feature. Now there are buttons to select or deselects all checkboxes, and a set of radio buttons to select full print, or just print preview for selected sheets. In addition, a checkbox was added to allow users to include or exclude the comment column when printing the Inventory page. User selections are 'remembered' via two new fields in column AB of the Lookup page (one for remembering checked items for print or preview, and one to remember the last used print/preview state). Thanks to Ken Robinson of Practical Financial Planning Ltd for the 'Include Comments' suggestion
09/05/12 Version 2.2.36
- Replaced missing header/footer color text labels in Lookup page Column AA (Planner Office Info)
- Deleted the default print header text.
09/01/12 Version 2.2.35
- Added code to import Risk Profile recommended I/E pct and last update date
- Changed the 'date' field on Risk Profile page from 'Global Date' to a user entered date
- On Investment Portfolio page, added code to highlight 'total percentage' field if it doesn't add to 100% (when the 'Enter Pct' choice is selected)
- Fixed a couple of small formatting errors on the Inventory page
- Added a 'Financial Life Cycle' page to display the static ACA Life Cycle Charts.
- Thanks to John Einberger of Mutual Advantage, LLC for these suggestions.
08/30/12 Version 2.2.34
- Revised several Lookup page named-range definitions to go down to row 40 instead of row 15 or 22. The effect of this will be to add these blank rows to the drop-down list for the associated fields in other pages (mostly the inventory page), and will allow for more extensive customization of these fields. Thanks to John Einberger of Mutual Advantage, LLC for suggesting this
08/20/12 Version 2.2.33
- Added a 'Header/Footer Color' field to the Lookup page in the Planner Office Info area (columns AA and AB) to allow a practice to change the color of all headers and footers. I also added a 'ACA Default Color' field in case someone changes the colors and later wants to change them back.
- Updated the Lookup Table Description document to show the new header/footer color feature
- Thanks to Ken Robinson of Practical Financial Planning Ltd for suggesting the header/footer color feature
07/30/12 Version 2.2.32
- Fixed a small formatting issue on the Investment Portfolio page
- Fixed the text in cell A8 of the Pyramid page so that it automatically changes from 'Projected Income' to 'Annual Living Expenses' whenever the life cycle stage is 'Financial Independence' or beyond. This was actually fixed quite a while ago on the Client page, but never propagated to the same label on the Pyramid page.
- Thanks to Ken Robinson of Practical Financial Planning Ltd for identifying these issues.
07/20/12 Version 2.2.31
- Changed colors of A1/A2/B1 blocks to a deep red, and changed C1/B2/A3 block to a deeper green, per John Einberger/Jo Anne Paynter request.
- Fixed bug that caused Investment Portfolio tab to 'jump' when activated.
- Made sure that both the C2 and A4 checkboxes are UNchecked by default.
- Thanks to John Einberger of Mutual Advantage, LLC for identifying the 'IP tab jump' issue
07/06/12 Version 2.2.30
- Added a 'Incl C2 in IP' checkbox (and associated link cell) to the pyramid tab to be consistent with A4 treatment
- Revised EMWB-to-EMWB pyramid import code to copy C2/A4 checkbox states
- Added code to pyramid tab Activate() routine to update Investment Portfolio Equity total formula based on the 4 possible A4 and C2 checkbox states.
- Thanks to John Einberger of Mutual Advantage, LLC for suggesting this
07/06/12 Version 2.2.29
- Added '*.xlsm' to File/Open and Import From File... routines so Excel 2007/2010 users can select 2007/2010 source files without having to manually change to the 'All Files' setting.
- Changed pyramid tab 'A Total', 'B Total', 'C Total', 'D Total' & 'E Total' cells to only display to nearest dollar. Thanks to John Einberger of Mutual Advantage, LLC for pointing this out
05/04/12 Version 2.2.28
- Revised for compatibility with Mac using VMWare/Fusion. Thanks to John Discepoli of Discepoli Financial for allowing me to run his Mac via remote control for debugging this.
05/02/12 Version 2.2.27
- Revised EMBW-to-EMWB import code for Lookup page to not terminate on a blank line in source file; now all source file rows are copied
- Revised Investment Portfolio so FAA Summary section properly checks Yes/No state of Lookup column V
- Revised so that a blank 'Company Name' field results in "Company Name" instead of "0" being placed on page headers
- Thanks to John Einberger of Mutual Advantage for bringing these issues to my attention
04/21/12 Version 2.2.26
- Revised to make the Inventory page the active page at the conclusion of a 'Paynter Pyramid' to 'Paynter Pyramid' import operation. This change ensures that the Investment Portfolio page is fully synchronized with the new inventory data the next time you activate it.
- Revised to move the cursor to the upper left-hand corner of the Inventory page after a 'Clear Inventory' operation.
04/20/12 Version 2.2.25
- Fixed a bug that allowed the state of the 'Enter %' and 'Enter $' radio buttons to be inconsistent with the state of the 'Pct' and 'Dollars' columns on the Investment Portfolio page, and a related bug that forced the button state to 'Enter %' each time the Investment Portfolio page was activated. Now the code correctly synchronizes the buttons and the edit state of the columns, and remembers which button the planner checked last. Thanks to John Dulmage of Financial Pathways for pointing this out.
- Added code to explicitly set the above buttons to 'Enter %' mode during an import from another 'EMWB' pyramid. This is done because the import operation always copies over the recommended percentages rather than the recommended dollar values.
- Added a 'Clear Inventory' button to the top of the Inventory page, for convenience. This will clear the contents of columns B-M and Q-U.
03/23/12 Version 2.2.24 (V2.2.23 skipped)
- Fixed a bug that caused radio buttons (and the associated hidden group boxes) to be randomly deleted in Excel 2010. The problem was caused by a change in the Excel Object Model between Excel 2007 and Excel 2010 that affected the VBA code used to implement the capability to display a company logo on all pages with a green header area by placing the desired logo image on the Lookup page.
01/16/12 Version 2.2.22
- Modified the Update Prices code so that it will offer to use "Previous Close" data if it can't find "Asset Value" or "Last Trade" values, with a warning to that effect. The user can opt to use the previous close value, or skip that inventory line. If only previous close data is available, you will get a warning on every inventory line. To supress the warning dialog for the current price update run, check the 'Dont show me this anymore' box. Thanks to Jo Anne Paynter of Partnership Financial LLC for bringing this to my attention.
01/09/12 Version 2.2.21 (V2.2.19 and V2.2.20 skipped)
- On the allocation page, there is now a 'Total by Type' line just below the bottom of the upper table, and a 'Update Totals by Type' button. Clicking the button searches through all the planner-added trade entries in the lower table, sorts them by type, and then sums the results in the 'Total by Type' line under the appropriate type column. Planners are then able to tell how well they are doing in matching trades to asset types. If an unknown asset type is encountered during the update, that cell is flagged (filled with red) and a warning message is posted. Thanks to Jo Anne Paynter of Partnership Financial for the suggestion.
- A 'CA Category Cross-Check' button has been added to the Inventory sheet. Clicking the button causes the code to search through the inventory, comparing CA category entries to the Asset Type code using the contents of Lookup table columns Q and T. If the Asset Type code on an inventory line doesn't match the column T entry for that type, the cells are highlighted in red, and at the end of the pass, a message pops up with the number of errors found. Thanks to John Einberger of Mutual Advantage LLC for suggesting this.
- The number of Lookup Table rows was increased from 22 to 40.
- The import function now copies any non-blank 'Factors Affecting Liquidity' comments from the Real Estate sheet of 'New' (2007) or 'EMWB' pyramids
- A detailed description of each column of the Lookup table has been added to the setup file, and is also available for download from the EMWB website. This document (Lookup Table Description.pdf) also includes a description of how Lookup table contents are used for automatically updating the Investment Portfolio and other sheets.
12/30/11 Version 2.2.18
- Added missing formulas to the top two rows of column C in the lower allocation matrix on the Allocation page. Thanks to Jo Anne Paynter of Partnership Financial for pointing this out.
12/29/11 Version 2.2.17
- Revised the formula for the A2 'Recommended' value on the Pyramid page (cell F36) from "=MAX(-0.2*(negativeA3),2*Pyramid_RecommendedA1)" to "=MAX(-0.2*(negativeA3+negativeB2+negativeC1),2*Pyramid_RecommendedA1)" in order to address the possibility of multiple real estate mortgage categories. Thanks to John Einberger of Mutual Advantage LLC for suggesting this.
12/28/11 Version 2.2.16
- Fixed a bug on the Allocation page that caused the 'Beg/End Balance' values in some colums to be calculated incorrectly. This bug was somehow introduced in Version 2.2.12, so only exists in V2.2.12 through V2.2.15. Thanks to Fran Goldman of Frango Financial LLC for bringing this to my attention.
- Added lines to both the 'Client View' and 'Advisor View' tables on the Trade Sheet page. Thanks to Fran Goldman of Frango Financial LLC for pointing this out.
12/23/11 Version 2.2.15 (no version change - just updated the installer program)
- Fixed a bug in the installer program (setup.msi) that prevented proper installation of the license management software on Windows 7 machines. Note: If you have already upgraded to Version 2.2.15, there is no need to do anything. If you are installing for the first time, then setup.msi will now work properly on both XP and Win7 boxes
12/20/11 Version 2.2.15
- Minor bugfix in code to clear upper allocation table - code didn't always reset all column headers to "Asset Class"
- Added code to properly add merged column sets to the upper allocation table as required.
- Revised the code to remove unused columns from the upper allocation table, in an effort to keep this table from getting ugly.
- Added code to remove gridlines from the Inventory page. Thanks to Jim Davis of Partnership Financial for this suggestion.
- Increased the maximum possible number of Retirement Plan codes from 10 to 15 (column H on the Lookup page). Now if planners add codes to this column they will show up on the selection drop-down list on the Inventory page. Thanks to Jim Davis of Partnership Financial for this suggestion
- Revised the Inventory page sort selection to include user-defined columns as well.
12/13/11 Version 2.2.14
- Changed 'Marketable Assets' to 'Net Worth' on the Ownership page data table. Thanks to John Einberger of Mutual Advantage for suggesting this.
- Fixed a bug in the Allocation page that caused a crash when the number of asset classes in an inventory exceeded the number of columns in the upper matrix. This problem was excacerbated by my attempt to 'clean up' the upper matrix in Version 2.2.13 - mea culpa!
12/9/11 Version 2.2.13
- Several planners had problems with the Pie Chart not showing up properly on the Ownership page, due to a blank line in the source table for the chart. This problem has now been fixed (again).
- On the Allocation Page, the 'Asset Class' row of the top table is copied to just under the 'Remaining' row, so the information will be available while working on buys/sells in the lower table. Thanks to Jo Anne Paynter of Partnership Financial for this suggestion
- On the Allocation page, Unused columns and rows are removed from the uppper table, and unused columns (not rows) are removed from the lower table. Jo Anne Paynter of Partnership Financial pointed out that extra rows in the lower table are a good thing, as room may be needed to input the necessary trades.
- On the Allocation page, a warning message is shown if a user clicks on the 'Get Data' button with an empty inventory.
- I was able to improve (but not entirely eliminate) the annoying flicker that occurs on some pages.
- On the Net Worth page, the subtotal line for the 'Other Assets' section sometimes went missing -this has now been fixed. Thanks to Fran Goldman of FrangoFinancial LLC for pointing this out.
12/9/11 Version 2.2.12
12/1/11 Version 2.2.11
- Added '1 Retired/1 Employed' employment status to Lookup page column E, and added room for 3 additional planner-entered employment status codes.
- Revised the VBA code for cell D36 on the Pyramid page to accommodate planner-entered employment status codes.
11/11/11 Version 2.2.10
- Updated the import routines for the Pre-Barrett, Barrett, and 'new' (2007) pyramids to specifically clear the recommended percentages on the Investment Portfolio pages. Percentages can't be copied from the source pyramid in these cases, as there is no correspondence between the source and destination categories, so we decided to make sure the destination percentages were cleared (set to zero) so that it would be obvious that these had to be adjusted by the planner. Note that with EMWB-to-EMWB pyramid imports, these percentages are properly copied over.
11/05/11 Version 2.2.9
- Fixed a problem that caused Excel 2007's Sort dialog to incorrectly populate the 'Sort by' fields. The fix was to select only the active inventory rows as opposed to the entire 500-row inventory table. Thanks to Jackie Kleinman of KB Financial Advisory for pointing this out
- Revised the 'Projected Income' label field on the Client page to automatically switch to 'Annual Living Expenses' whenever the Life Cycle Stage changes from 'Building...', 'Early Accumulation', or 'Rapid Accumulation' to 'Financial Independence' or beyond. Thanks to Jo Anne Paynter of Partnership Financial for this suggestion
- Fixed a bug in the 'New' pyramid to EMWB pyramid import routine that incorrectly copied UserDef1 & UserDef2 column information into the EMWB pyramid's 'SubType' and 'Index' columns. Now it correctly copies them into the 'UserDef1' and 'UserDef2' columns.
10/26/11 Version 2.2.8
- Fixed a bug that caused a run-time error on the Ownership page by some new code to set the pie chart section colors to 'Automatic'. Thanks to Jim Davis of Partnership Financial for pointing this out.
10/18/11 Version 2.2.7
- Fixed a problem on the Risk Analysis page where the left and middle radio buttons on the top row were somehow tied together, so clicking one of them also 'clicked' the other. Thanks to Jo Anne of Partnership financial for pointing this out..
- Addressed a potential issue on the Ownership page: If the total of an ownership category like 'Joint' (J) is negative, it doesn't get included in the pie chart or the source data table. While this is correct (can't have negative pie chart areas), it could be misleading to a planner or client. So, now if the code encounters a negative value for an ownership category, it alerts the planner with a message box saying something like "Caution! Total for J is negative (-22046.221) - Skipping!!". Thanks to Jo Anne of Partnership Financial for showing me a pyramid Ownership page that wasn't displaying anything for the 'J' (Joint) category due to an odd split in ownership of a house asset/mortgage
- Fixed problem where gridlines keep coming back for some reason. Now each sheet's gridlines are turned off explicitely in its 'Activate' event
10/17/11 Version 2.2.6
- UnHid Lookup columns N, O, P, and T, as this caused problems with some of the AutoFilter operations required to update the Investment Portfolio pages. Instead, column N cells were given a white background and 'locked' to prevent user modification of the fixes FAA categories.
- Added code to copy Investment Portfolio recommended percentages from the source pyramid in 'EMWB-to-EMWB' pyramid imports.
- Re-checked the 'automatic color' choice for the Ownership sheet pie chart. This had gotten inadvertently UNchecked, with the result that the all the sections of the pie chart were shown in the same color.
- Removed gridlines (again!) from most sheets
10/15/11 Version 2.2.5
- Hid Lookup columns N, O, P, and T to prevent user modifications. Column N contains the fixed FAA summary categories and should never be changed. The other columns are left-overs from the 2009 specification that are not used. Thanks to new pyramid user Tim Caban of Copper Beech Advisors LLC for this suggestion
- Removed (actually just hid) the 'Risk Adjusted' default columns from the Investment Portfolio sheet. Thanks to Howard Cadwell of Northeast Passage Financial Advisors LLC for this suggestion.
10/15/11 Version 2.2.4
- Fixed a bug in the 'paynter-to-paynter' pyramid import routine that prevented proper copy of Lookup table column U. Thanks to new pyramid user Tim Caban of Copper Beech Advisors LLC for pointing this out
10/14/11 Version 2.2.3
- Added percentages to the summary rows on the Pyramid sheet. These were in the 2007 version of the pyramid, but got dropped in the Core Tools Committee specification published in 2009. They are now back in by popular demand
- Removed (actually just hid) the 'Default Risk Adjusted' columns (Columns F and G) on the Investment Portfolio sheet. These were from the Core Tools Committee specification published in 2009, but apparently were found to be more confusing than useful. Note that the columns were HIDDEN, not removed, as that way the in-sheet references didn't get screwed up.
- Fixed a problem with the print range on the Risk Profile sheet - thanks to Jacob Kuebler of Bluestem Financial Advisors, LLC for pointing this out
10/01/11 Version 2.2.1
- Added 'selective print' feature. Now when the 'Smart Print' button on the Inventory sheet is pressed, a dialog with a checkbox for each available sheet appears, allowing selective page printing. If the Inventory page is selected, only the active rows are printed, just as before.
09/30/11 Version 2.1.333
- Revised the Inventory sheet to 'freeze' rows 1-4 so the column headers don't disappear when scrolling, and revised the code that makes sure that cell A1 is at the top-left corner of the Inventory sheet after a price update operation to accommodate the 'frozen' rows. Thanks to Rob Reed of Reed Financial for suggesting this change.
- Fixed a bug that 'undid' the first bugfix described in V2.1.331 below. The original bugfix was to make sure the Inventory sheet got set to 'UNprotected' when the spreadsheet is first opened. If the Inventory sheet becomes protected for any reason, then some of the VBA code may fail.
09/28/11 Version 2.1.332
- Revised the code that imports from the 'new' (2007) pyramid to properly bring in the net worth history and planner notes. Thanks to John Dulmage of Financial Pathways for pointing this out
09/27/11 Version 2.1.331
- Added code to the startup routine to ensure that the inventory page is UNprotected - this fixes a number of protection-related problems.
- Fixed the problem on the trade page that prevented the 'Client View'/'Advisor View' toggle button from functioning correctly when a logo image was used instead of text . As it turned out, this same problem occurs on the Net Worth and Investment Portfolio pages. Now all operate properly with or without a logo image.
- Fixed the problems associated with the logo image. These problems were caused by not properly unprotecting pages before attempting to delete a logo image.
09/27/11 Version 2.1.330
- Removed the logo image from the Lookup page, as this was causing problems with some Excel 2007 installations. Once I have the problem figured out and fixed, I will restore this feature
07/11/11 Version 2.1.329
- Cleaned up some VBA code that could cause a sheet to be re-protected even if a user wanted it to remain unprotected
- Added the PF logo to the Lookup page as an example of using a Logo image for each sheet header instead of a text label. Replace the PF logo with your own, or delete to have the text re-appear..
07/11/11 Version 2.1.328
- Jo Anne Paynter of Partnership Financial pointed out that the bug that caused a '0' to appear in the stock symbol column after an import is also present when a Barrett or 'New' (2007) pyramid is imported, and it also causes the 'Current Price' column to contain a 0 value. This version fixes the bug for Barrett, 'New', and 'Paynter' pyramid imports.
07/10/11 Version 2.1.327
- Fixed a bug that caused '0' to appear in the stock symbol column when importing from one 'Paynter' pyramid to another. The '0' caused the 'Update Prices' function to try to find the current price for a '0' stock (and fail). Then it colors that cell red - not really a bug, but definitely an annoyance!
- Added a 'Client/Advisor View' button to the Trade Sheet. The 'Client View' hides all the advisor-entered detail columns to allow a simpler presentation to clients. The 'Advisor View' un-hides all these columns. .
07/07/11 Version 2.1.325
- Revised to accommodate a change in Yahoo's stock price reporting page (http://www.finance.yahoo.com) that caused the Update Prices function to fail.
- Fixed a buglet that allowed the license and version information on the 'Lookup' page to be overwritten when information from an older ACA Pyramid was imported into a newer one. Now only the first two rows of the 'Planner Information' column are copied over on the import.
04/20/11 Version 2.1.324 (no version change - just updated the installer program)
- Changed the installer's default installation folder from C:\Program Files\ACA Pyramid to C:\Documents and Settings\All Users\ACA Pyramid to (hopefully) solve problems caused by Windows 7's security model, which refused to allow a required install utility to run properly from the C:\Program Files.folder. There is no change in the program's function, just the way it gets installed.
04/03/11 Version 2.1.324 (no version change - just updated the user guide)
- Updated the User Notes to include a more detailed explanation for how the Current Allocation matrix is constructed. Thanks to John Dulmage of Financial Pathways for pointing this out.
03/23/11 Version 2.1.324
- Expanded the 'Ownership' table (J3 through L15).on the lookup page to include 4 'User Defined' ownership categories. The default categories are 'USR1 - USR4', but can be changed to anything desired. Thanks to Howard Cadwell of Northeast Passage Financial Advisors for suggesting this.
02/27/11 Version 2.1.323
- Added a checkbox next to the 'A4' section on the Pyramid page. Checking the box will change the Investment Portfolio formula in the Equity column on the Pyramid page to include 'NetA4'. Unchecking the box will omit A4 from the calculation. Note that this has no effect on the Investment Portfolio page! Thanks to Ed Fulbright of Fulbright Financial Consulting for pointing this out.
02/07/11 Version 2.1.322
- Fixed a problem that prevented several pages from refreshing properly if the user inadvertently entered something in Column A of the inventory page. Thanks to Jo Anne Paynter of Partnership Financial for pointing this out..
- Removed background gridlines from most pages. These can be restored if desired via the Tools/Options/Gridlines checkbox
12/09/10 Version 2.1.320
- Fixed a bug that caused the 'Current Price' value to be zeroed out when an inventory row contains an invalid stock symbol. Now the value remains unchanged, but the cell fill color is still change to 'red' to indicate a problem. Thanks to Reggie Allen of Moneyful.com for pointing this out.
12/08/10 Version 2.1.319
- Automatically add rows as necessary to the lower table on the Allocation page. This was already being done for the upper table.
12/02/10 Version 2.1.318
- Added code to prevent users from modifying the fixed (non-user-defined) column header labels on the Inventory page. Now if a column header cell is selected, the selection jumps to the cell just above.
11/27/10: Version 2.1.317
- Revised the default pie-chart segment colors.
11/25/10: Version 2.1.316
- Revised the Investment Portfolio page to replace the 'Investment Portfolio' text with the date and client name from the ClientInfo page. This was done so the pyramid revision date and client name would be visible if the page was printed.
- Fixed a problem that caused the Update Prices feature to terminate prematurely. This was due to the http request returning with an error that was not properly trapped. Now the pyramid traps the error, colors the offending cell red, and moves on to the next inventory row.
- Added code to display the user's license key on the lookup page, in case it is needed for any reason
- Added a EMWB-to-EMWB import feature to allow easy updating of pyramids when new bugfix or upgrade versions are released. This feature copies the inventory, client info, and lookup pages from the source to the destination. Unfortunately, I haven't figured out how to copy any pie chart color customizations, so that will have to be copied manually for now.