Spreadsheet syntax

Spreadsheets cannot be edited within the Regime Library workspace, so they must be pre-prepared in an external application.

This topic gives details of the syntax to be followed when preparing a spreadsheet.

Named Range syntax

One or more named ranges must be created in the spreadsheet to specify an area (or areas) of data that will be transferred between the spreadsheet and Planning Space. (If there is more than one valid named range detected by Planning Space, then a named range must be selected in the Spreadsheets management tab for the regime.)

Planning Space looks for named ranges with a specific form: the first (top left) cell contains the heading text 'Type' and the following column headings (in the same row) contain only allowed values, as defined below.

The named ranges can be located on any sheet in the workbook. Cell references within a named range area can refer to anywhere else in the workbook. For example, you could reserve one sheet for the named range for data transfer, with calculations being performed on other sheets.

Column headings

The allowed column headings are:

• Type
• Variable
• Unit
• Partner
• Context
• Project Index

Periodic data is placed in columns without a heading.

'Type' column allowed values

This value specifies the type of content for the worksheet row. The following values may be used:

• PERIOD: specifies a row that will bring in date ranges from Planning Space
• IN: an input variable from Planning Space to the spreadsheet
• OUT: an output variable to return data to Planning Space
• TABLELOOKUP: lookup values associated with a table variable
• TABLERESULT: numeric values associated with a table variable
• CONTEXT: access to project pseudo-variables within ringfenced modules
• PRICE: price values associated with a product stream
• PRICEBYNAME: price values associated with a price deck name
• WI: partner working interest values

If the Type value is blank, the row will be ignored by Planning Space, hence the row could contain explanatory comments or internal calculations.

When the Type is set to PERIOD, the Variable column must contain one of the following values:

• START: start of day of the first day in each period
• END: end of day of the last day in each period
• DAYS: the number of days in each period
• NAME: a string that specifies a period (such as '2017 Q1')

The periodicity of the data is defined in the module and it should match the expected periodicity in the spreadsheet.

Variable references

References to variables use the same syntax as in the module Logic Editor: brackets [...] for ordinary variables and the @ prefix for temporary variables. However you need to use a apostrophe (single quote, ' = unicode 0027) in front of @ to escape the meaning of the symbol in Excel.

Units are specified in the Units column. If left blank the base units (as in Planning Space Economics) will be used. If a Unit is specified that does not exist in Planning Space an error will be reported.

Table variables (for lookups)

The Type TABLELOOKUP will receive lookup values, and TABLERESULT will receive the associated numeric values. Units are optional for TABLERESULT, and are ignored for TABLELOOKUP.

For example, the Planning Space tables shown below are set up as shown in the spreadsheet:

Note that a table variable can only be specified as inputs to the spreadsheet.

Price syntax

There are two ways to use price deck information:

• The PRICE Type is equivalent to the GetPrice function and the Variable column value specifies the name of the product stream. The row will be populated with prices based on the specified product stream.
• The PRICEBYNAME Type is equivalent to the GetPriceByName function and the Variable column value specifies the price name from the price deck used by the hierarchy.

PRICE will be ignored in ringfenced modules because each project can have a difference price per stream.

The Variable column must be either the name of the product stream, or the price name. The Partner value must be blank.

The Unit value is optional. If specified, there is limited functionality for metric/imperial conversion of units. Scale conversion and currency conversion are not allowed. For example to bring in Oil Price for a regime in USD the allowed Unit values are 'USD/Bbl' or 'USD/m3'.

The images below show an example of price deck settings in Economics and how they can be used in a spreadsheet:

Ringfence syntax

It is possible to access individual project-level data within the context of ringfenced modules. The column 'ProjectIndex' is used to qualify the variable in the row:

• Blank value or zero brings in the aggregate value for the ringfence.
• Integer value greater than 0 brings in the values for the ringfence member with that index (equivalent to the set of functions GetProjectNameForRingfence, etc. in the Logic Editor).
• Output variables to Planning Space must have ProjectIndex value blank or zero.

The CONTEXT Type provides access to the following pseudo-variables of the member projects that are indexed by the ProjectIndex values:

• ProjectCount: returns the number of projects within the ringfence. It will return 1 if the module is not ring-fenced (ProjectIndex does not apply).
• ProjectName: scalar text with the name of the member project.
• IsEconomicLimitSpecified: scalar Boolean that indicates whether the user has manually-specified the economic limit date.
• SpecifiedEconomicLimitDate: a scalar date that contains the user-specified economic limit.

Partner values syntax

This provides equivalent functionality to the PartnerValue function in the Logic Editor. It is only applicable to IN or OUT Type rows. You should be aware of the module calculation sequence for partner data to use this functionality; see Calculation Phases.

The optional column 'Partner' is used to specify the partner that the variable in the row is referring to. If blank the reference is to the current calculation partner (i.e., same as the default behavior). The Partner values are not validated by Planning Space. For a non-existent partner, IN variables will remain blank and OUT variables will be ignored.

Working interest syntax

This functionality is equivalent to the functions GetCurrentPartnerWI and GetPartnerWI, to bring in working interest information to the spreadsheet.

The Type must be set to WI. The Variable column value should contain a WI category name; if the value is blank no data will be brought over. If the category name is not valid then the Default category will be used (this is the same behavior as the Logic Editor functions). Note that string matching for values is case sensitive.

The Partner value is optional, if blank the current partner values will be used. The Unit and ProjectIndex values must be blank.

The images below show an example of working interest share settings in Economics and how they can be used in a spreadsheet: