Make or export a new component from an existing Excel model

Openbox now includes the ability to convert existing spreadsheets to Openbox components. This means that you can base all future models on the best ones you already have. 

First of all, open the Excel file you want to make a component from. You then have two ways to make or export the component.

1.     The ‘Make component’ option

Select the rows you want to read in and click ‘Make Component’. You can select the whole row, or just one cell per row – it doesn’t matter.

Then click “Make from selection” in the Openbox part of the Excel ribbon, as shown in the picture below.

Openbox will read the values, formulas, labels and units in that section and convert them to an Openbox model. If you already have an Openbox model open, the new calculations will be added to that one. Otherwise, a new model will be opened.

2.     The copy and paste option

Select the rows you want to read in, then press Copy. Switch to Openbox and press Paste (Edit-Paste or Ctrl+V). You will be asked to confirm which column in your spreadsheet has which purpose.

The result in both cases will be an Openbox diagram showing a sheet, and a box for each calculation you selected. The picture below shows an example.

You can then create a component by selecting the “Imported” sheet, clicking “Insert”, the three dots, then “Export component”.

Openbox will check it for errors or issues, then ask you for a description. This description will be shown to anyone who uses the component in future, so it should be descriptive and clear as to the component’s purpose.

Openbox will then ask you where it should save the component, and what the filename should be. Once you click OK the component will be saved i.e. exported to disk. You or other can then use it in all future models.

What kind of Excel models can I use?

This process will work for models that follow standard good practice. In particular, there should be one unique formula per row, and each column should have the same purpose on each row. 

The model does not have to be to the FAST standard. Openbox can read FAST models, but it can also read others too, providing they fit the best practice requirements above.

Note that Openbox will validate the model it creates, and warn you of any issues it finds e.g. inconsistent units. This can be a useful validation of your existing models.

In some cases, Openbox cannot read the model even if it is best practice. Typical reasons include:

• You have used the same label for two or more lines. Openbox doesn’t allow duplicate labels.
• You have used a complex lookup or INDEX/ MATCH function which looks over several rows.

If it cannot read part of the model, Openbox will tell you what the problem is, and where it is. In most cases, it will still read the rest of the model, and show placeholders for the parts that it could not read. You can then edit these manually in Openbox.

Component best practice

We have found that these rules help you to produce components that are clear, and easy to reuse.

Use SMU rather than currency units

When you bring a component into a model, Openbox will automatically replace ‘SMU’ in the component with the model’s default unit. This means that a component with SMU can work for any currency, making it much more flexible.

Do not use POS or sign switch lines in the Excel you are reading in

Some financial modelling standards use POS and sign switch lines, where a line item is used on a report. But standard components do not contain reports. The line items in a component might or might not be used on a report in any future model. Openbox will insert these lines automatically if needed, so there is no need to put them into a component.

Break circularity using Openbox, not manually

Openbox can recognise and break circular references in a model. If you break circularities by right clicking on an item and choosing “Break circularity”, Openbox will create copy/ paste blocks for that item. When you then import the component into a model, these blocks will be automatically consolidated into the overall model copy/ paste block. This consolidation won’t happen if you create copy/ paste items manually, because Openbox won’t recognise them as copy/ paste items.