Excel upload of Assumptions & Line-Items
You can download all data of an Assumption as an Excel file with one sheet per Line-Item. You can edit the data and upload the file to the same Assumption again.
Download Excel is a submenu with two options, and the choice decides whether the file round-trips:
- Line-Items in “All Details” view is the raw per-line-item data table. Download this one when you intend to edit the file and upload it again.
- Line-Items in their current view reflects the active filters and the chosen Change, Result or Absolute columns. It is a report, not a re-uploadable source.
An upload replaces the data, it does not merge into it. Rows that exist in the line-item but are missing from the file you upload are removed. Every sheet in the file must map to a line-item variant, or the upload fails with ‘Sheet does not match any assumption’, and every variant must have a sheet, or it fails with ‘Uploaded XLSX file is missing sheets for assumptions’. Partial uploads are rejected.
In some cases you want to upload the data to new or other Assumptions, either in the same or other workspaces. Refer to the following tables about supported use cases:
| Task | Supported |
|---|---|
| Creating your own Excel file in the same structure | Yes, the system matches the Sheet and Assumption Line-Item name. A leading (1.1) index, which downloaded files carry, is stripped before comparing, so (1.1) My line-item and My line-item both match. Beyond that the sheet name must match exactly, and Excel caps sheet names at 31 characters, so a long line-item name is truncated on download and has to be reproduced truncated. |
| Uploading the downloaded Excel Sheet to another Assumption with the same structure (for example a duplicated Assumption) | Only if the file has no Metadata sheet. With one, line-items are matched by their UUID, and a duplicated assumption gets fresh UUIDs, so the upload fails with ‘Line-item with uuid … not found.’ rather than matching by sheet name. Works for a single line-item with one unfiltered variant, or after deleting the Metadata sheet from the file. |
| Filling in the data of a downloaded file via formulas | Yes |
| Changing Level Values to existing Level Values | Yes, if they are included in the Assumption Line-Item Filter |
| Having additional Sheets in the upload file | Yes, but they must start with the number symbol (#) |
Uploading all assumptions at once takes a ZIP file. The single-assumption upload accepts
.xlsxand.xlsm. The workspace-wide upload, reached from the import and export menu of the Scenario Manager, accepts only a.ziparchive holding those Excel files; its dialog is titled ‘Upload All Excel Files as a Single ZIP File’.
The Metadata sheet
Most downloads carry an extra first sheet named Metadata. Each of its rows gives a Line-item name, a Line-item UUID, a Variant name and the Sheet they belong to, and that is what an upload uses to put every sheet back where it came from. Keep the sheet in the file, and keep those four columns: a missing one is rejected with ‘The ‘Metadata’ is missing the mandatory column ‘…’.’
Valsight adds the sheet whenever the assumption has more than one line-item, more than one variant, or a filtered or non-default view, and always for a workspace-wide download. Only the simplest case, a single line-item with one unfiltered variant, comes without one.
The sheet is not required on upload. Without it, Valsight falls back to matching sheets by name, which is what the table above describes.
Data format
Time in rows
Use a standard upload format with years in rows for your Assumptions.
| Country | City | Year | Change |
|---|---|---|---|
| USA | Boston | 2021 | 100 |
| Germany | Berlin | 2021 | 200 |
| USA | Boston | 2022 | 300 |
| Germany | Berlin | 2022 | 600 |
| USA | Boston | 2023 | 500 |
| Germany | Berlin | 2023 | 1000 |
Time in columns
Use an upload format with years in columns.
Tables like this are typically used in financial data tables.
| Country | City | 2021 | 2022 | 2023 |
|---|---|---|---|---|
| USA | Boston | 100 | 300 | 500 |
| Germany | Berlin | 200 | 600 | 1000 |
Correct data upload for years in columns.
| Engine | 2020 | 2021 | 2022 | 2023 |
|---|---|---|---|---|
| Jet engine | 1 | 1 | 1 | 1 |
| Ottomotor | 1 | 1 | 1 | 1 |
Possible error messages when uploading data
Error message: “Parameter Table must contain exactly one measure column, but contains: ‘[Paris, Augsburg, Berlin, Hannover]’”
Error: When this error message appears, a different level than time has been used in columns (horizontally).
Example:
| Year | Paris | Berlin | Munich | Hamburg |
|---|---|---|---|---|
| 2020 | 12 | 12 | 12 | 12 |
| 2021 | 12 | 12 | 12 | 12 |
| 2022 | 12 | 12 | 12 | 12 |
Error message: “Parameter table must contain exactly one measure column, but contains: ”[]”
Error: When this error message appears, a merged column has been used as highest column horizontally.
Example:
2020 | |||
2020-Q1 | 2020-Q2 | 2020-Q3 | 2020-Q4 |
1 | 1 | 1 | 1 |
1 | 1 | 1 | 1 |
Error message: “Column ‘2020.0’ could not be identified. Use a level name or Measure/Change or Comment.”
Error: When this error message appears, either data is uploaded into a year that is not within the project horizon or the uploaded excel contains multiple (time) levels as column headers.
Examples:
| 2020 | 2021 | ||||||
|---|---|---|---|---|---|---|---|
| 2020-Q1 | 2020-Q2 | 2020-Q3 | 2020-Q4 | 2021-Q1 | 20201-Q2 | 2021-Q3 | 2021-Q4 |
| 12 | 12 | 12 | 0 | 0 | 0 | 0 | 0 |
2020 | |||
2020-Q1 | 2020-Q2 | 2020-Q3 | 2020-Q4 |
1 | 1 | 1 | 1 |
1 | 1 | 1 | 1 |
Error message: “Missing data on ‘City’ levels ‘Profit’ which are required by Assumption ‘{2}’ / New parameter data contains duplicate values for ‘[2020]’”
Error: When this error appears, the uploaded Excel file is missing a column header for a level.
Example:
| 2020 | 2021 | 2022 | 2023 | |
|---|---|---|---|---|
| Paris | 12 | 12 | 12 | 12 |
| Berlin | 12 | 12 | 12 | 12 |
| Munich | 12 | 12 | 12 | 12 |
| Hamburg | 12 | 12 | 12 | 12 |
Not supported uploads
Uploading Excel Sheets with merged cells within the data is not supported and an error message will appear.
The download you edit and send back is normally the change view, so upload changes rather than target values. Uploading target values is possible, but only if the
Metadatarow for that sheet sets its view to the result view and names the scenario the values apply to. Get either half wrong and the upload is rejected with a message telling you which.Tables may only contain upper case letters (e.g. 2021-Q1 instead of 2021-q1)