Financial modelling productivity pack release notes
Download the latest version from the Productivity Pack download page.
Version 028e - Updated January 28th 2023
Several bug fixes plus the following new additions and updates:
New additions
Avoid linking to blank cells when copying a link across
One of my training clients requested this change. When you're creating a link, and you link to the row label, then use Ctrl+Shift+a to copy across, you're left with links to blank cells.
While they liked the calculation block structure and the use of links, they didn't like having these links to empty cells.
We have added a user setting that prevents the links to blank cells from being created. In the User Settings sheet, change the setting shown below to FALSE to have links operate in this way.
This macro uses the Column Use definitions to determine which columns not to link to. Links to blank cells will be prevented in the Constants, Units and Totals columns and the blank column before the start of the timeline.
Remove existing links to blank cells from
A new function has been added to the Ctrl+Shift+6 utilities that will remove links to blank cells from selected sheets. This allows users who do not want these links to remove them from an existing model.
Select Option 9 from the Prepare Model for Release list.
Updates
Change to sheet list in Ctrl+Shift+6 utilities
There was an issue with the Ctrl+Shift+6 utilities where there were hidden sheets in the model. The macros would unhide the sheets to perform an operation and then leave them unhidden.
We have updated the sheet list in the Ctrl+Shift+6 utilities to show which sheets are hidden and which sheets are protected. The use of square brackets around the section headings is pretty ugly, but unfortunately, the formatting choices in VBA dialogues are limited.
As well as giving more control over the functions that can be run across sheets, it's a useful way of seeing any hidden or protected sheets in your model.
We've added buttons to make it easier to toggle between selecting all sheets and selecting and de-selecting hidden sheets that you may not want to perform a function on.
Protected sheets are always excluded from any of the processes.
Now, if hidden sheets are selected for an operation to be applied, they will be unhidden for the macro to run and returned to their hidden state at the end of the process.
Version 025d - Updated November 28th 2022
In addition to numerous bug fixes, there have been several new additions to the Productivity Pack and updates to existing functionality.
New additions
AI translation tool
Addition of a new keystroke allocation (Ctrl+Shift+4) to translate row labels. This is to allow easy translation of the labels in the model into another language.
See: AI multi-language model translation
Named Ranges report
A new report, accessed from the Model build utilities dialogue (Ctrl+Shift+6, M) will list all the Range Names in your model. This helps to identify Names containing errors, unused Names and hidden names.
See: How to list all the Named Ranges in your model.
The Named Ranges report can be used to select particular Names that you'd like to delete. This could be because they have errors, are not used or are hidden Names that you'd like to get rid of. Access from the Model build utilities dialogue (Ctrl+Shift+6, D)
See: How to delete unwanted range names
Unique formulas report
This shows a list of all unique formulas in the model. This can be useful when sending a model for audit. It's also a good checking tool for generally reviewing formula length etc. Access from the Model build utilities dialogue (Ctrl+Shift+6, O)
See: How to list unique formulas in your model
Updates
Model columns defined in user settings. Since not all models follow the column definition in base training models, column usage can be configured in user settings.
Ctrl+shift+c (clear shading) also clears sparklines.
Update to keyboard shortcut configuration. All macros listed with a drop-down for keystroke modifiers and the ability to selectively activate or deactivate specific macros.
Improvements to conditional formats list. The layout of the conditional formats report has been improved.
Capitalisation check doesn't require placeholders to follow capitalisation rules.
Used range list. Updated to include sheet visibility, protection, count of formulas, error values, validations, format conditions. (add to model release checklist). The used ranges report is now added as a sheet in the active model - not as a separate workbook.
Used row labels. The report is now sorted in reverse order by number of occurrences of a label. This is used to check for duplicate row labels - potentially indicating that something is being calculated twice in the model, or that links have not been properly created. The report is now created as a sheet in the active workbook rather than in a new workbook.
Version 020a - Updated April 1st 2022
This update was "under the hood" code review & tidy up, and changes to ensure all shortcuts work on Excel for Mac.
New keystrokes for Mac users.
All Ctrl+alt keystrokes on Windows are now Shift+Ctrl+Option on Mac.
Windows keystroke change
Adding variance tabs was previously activated using Ctrl+Shift+3. This keystroke is not usable on Mac. We have therefore moved to Ctrl+Shift+9 on both Windows and Mac. Guidance on using this macro to follow as it's not yet been added to the handbook.
There is a known bug on the changelog macro Ctrl+shift+1. This will be addressed in the next version.
Version 18o - Updated March 16th 2022
New: Add a Sparkline
See this guide on adding a sparkline to a calculation.
Version 18n - Updated March 15th 2022
Bug fixes & minor updates:
- Reset end cell macro didn't clear out formatting.
- Used Range report didn't correctly report last column in extreme cases
- Adding to the capitalisation exceptions list now checks for duplicates and reports
- Changing the sheet exceptions for import / export marking now autosave
Version 18m - Updated March 12th 2022
Managing exceptions for import/export marking and capitalisation macro
Improvement to the interface to make it clearer which sheets were excepted for import marking and export marking.
- Managing import and export marking exceptions
- [Capitalisation checking]
Bug fix: Macros don't work on protected worksheets
Certain macros were not working where some sheets in the model are protected with a password.
Version 18j - Updated March 2nd 2022
Updated: Reset end cell
This was not working consistently on all files. Works consistently now but further testing and user feedback across more file types will be useful.
Updated: Sum of the row above
When the new is used in the constants column to give sum of the series values in the row above, it now also copies down the units from the row above.
Update: Input relocation macro
Now contains a dropdown for the sheet that you want to send the inputs to. Only asks once for constants, and once for series, in each session then defaults to those options for future relocation
Input relocation macro adds constants to the active case. Range name CASE_ACTIVE is required to tell the macro which case is active.
Update: Add to exceptions list
Previously to add a sheet to the exceptions list, you had type the sheet name. There is now a dropdown to select the sheet name.
Update: Label capitalisation check - "spell checker" type behaviour added
Previously the macro to check label capitalisation would make corrections all over the sheet without any user control. The macro will now make suggestions and allow the user to decide on a case by case basis, like a spell checker dialogue would.
Version 18d - Updated 22nd February 2022
New: Unhide and hide the macros
Previously when we wanted to hide and unhide the macros, we had to do it through the menus. We can now do this with Ctrl+Shift+7. This keystroke toggles the macro workbook between hidden and unhidden.
New: Delete a worksheet
Using F11 regularly to chart parts of your model leaves you with the need to delete sheets regularly. We have therefore added a shortcut for this. You will get the same "are you sure you want to do this" warning as would get using the menu commands.
New: Reset the end cell
The behaviour of this shortcut depends on where you are in the sheet:
- If you are in column J or later, the shortcut will set the end column
- If you are in column A through I, the shortcut will set the end row
New: Add to output sheet
You can only run this shortcut on constants or row totals. The macro will create a link to the selected item on the Output sheet.
You need to maintain a template row on your Output sheet, named "OutputRowTemplate". The Handbook start file has the correct range names for reference.
See also this summary of the range names needed in your model for the macros to work.
Updated: Row total
This keystroke has a new behaviour. When run in the Row Total column, it will add a row total as usual. If we run it in the Constants column, it will add a total of the line above as a constant and add "Sum of " & [above row label" as the row label. It will copy the units from the row above.
We have added this because we regularly need to bring the total of a series row into the constants column to be able to perform further calculations using that total.
Updated: Utilities dialogue
This dialogue has been renamed from "Prepare for release" to "Utilities".
Changes to the dialogue
- Select all button added for when we want to run a utility on all sheets.
- Shift+down arrow works to select ranges of sheets to apply the utilities.
The utilities have been grouped into three types:
- Quick build. These utilities help you get small build tasks done quickly, such as adding a new corkscrew or a new template sheet.
- Prepare model for release. These are a series of "tidy up" operations that can be run on selected sheets.
- Model information. These run on all sheets and return information about the model.
New utilities have been added:
- Collapse/expand all grouping. This will toggle the grouping state on all selected sheets.
- Change header. This will change the print header across all selected sheets. Helpful in updating, for example, a Project Name across all sheets
Other changes
- The utility macro to reapply grouping no longer hides the rows after the "END" marker at the bottom of the sheet.
- Changelog. The spacing settings have changed on the control log (Ctrl+Shift+1). Spacer rows are only inserted between change log sections.
- We've made changing the keystrokes for macros easier by adding modifier keys to a dropdown menu. They previously had to be typed, which was error-prone.
Comments
Sign in or become a Financial Modelling Handbook member to join the conversation.
Just enter your email below to get a log in link.