Manual Mapping does not require you to add a sheet to your workbook. It is most useful if you have no control over the source file (eg: it comes from a 3rd-party data provider or from another software you use).
Follow the steps below to map the data on a file manually.
Map it once, no need to do it again
If this is not the first time you are uploading this file and you have already mapped it before, Sentieo Connect will recognize it and offers to process it automatically.
You do not have to remap files unless the relevant data have changed substantially; Sentieo Connect is robust to small alterations such as adding lines and columns, even within the target area.
Step 1. Upload the File
Start by uploading the file using the Excel plugin or through SFTP.
Sentieo Connect will invite you to start mapping the data if the file does not have an Upload Template sheet and it was never seen before.
Step 2. Provide Some Additional Info about the Workbook
This step is the preparation phase of the data file. You can configure a certain number of optional features that help get the file ready for ingestion with Sentieo Connect.
- Define a Threshold: When processing the file, you can define an error tolerance for erroneous datapoints. For example, a threshold of 5% means that the upload will be considered successful as long as at least 95 datapoints out of every 100 datapoints uploaded are valid. A valid datapoint is a datapoint for which all the following information is present:
- a ticker,
- a field,
- a period (if the field is periodic),
- an as-of date,
- a scenario,
- an actual datapoint, and
- the datapoint is of the right nature for the selected field (eg: number <> number).
These details can either be directly provided in the table or by using appropriate fallback options (see more about fallbacks below).
- Load An Existing Mapping: Instead of starting the file mapping from scratch, you could base it on a mapping configuration you made previously for another file. You’ll find here all the configurations that have been created by you and your colleagues.
- Find and Replace: Works by creating a list of Strings you want to look for in the file and get them replaced by something else, before you move further in the file mapping process. Create Find and Replace lists from the Sentieo Connect Portal on the webapp
- Set up variable: You can pick up fragments from the file name as variables and use these to define the name of the watchlist you are creating during the uploads.
For example,, if the file name is “220415 – Star_Portfolio v5_export.xlsx”, you can express this as “* – %variable1%_*”
- * means “ignore whatever happens between the next pattern” => it will ignore 220415
- – %variable1%_ means find “ – “ and “_” , and put everything in between a new variable that you will call variable1 => this will be here “Star”
- * the last star means ignore everything else after.
=> in this example, we are creating one variable called variable1 and which value is “Star”.
Assuming the structure of the file and of the file name is static, you could upload next time a file called “251212 – Laggers_toconsider.xlsx”, and it will automatically pick up as variable 1 “Laggers”.
You can use variable1 as part of the name of your watchlist (eg: “%variable1% – uploaded”), so the watchlist you create or update here will be called “Star – uploaded” or “Laggers – uploaded” for the second file.
- Delimiters (CSV files only): CSV files do not have a standardized method of representing the delimitation between cells on the spreadsheet. Some files use commas (,), others use semi-colons (;), or a pipes (|), and so on. Please select which one your file uses. If you are not sure what your file uses, open it with Notepad on windows and see what delimits each value from the other.
In this example, it is a comma
- [Date Format] (CSV files only): Unlike Excel files, CSV files do not share a standard format for expressing dates. Pick here the date format which is used in your CSV file (eg: dd-mmm-yyy; or mm-ddd, yy, etc..)
Step 3 – Locate in the Workbook the Table with the Data to Upload
In the next step, focus in on the workbook area with the relevant data to be uploaded
- Worksheet: Select the worksheet with the data
- Start Row and Start Column: Indicate the number of the line and column where the data starts
- End Row and Column (both are optional): use only if you want to manually hardcode the end row and/or column of your table.
If you leave those empty, Sentieo Connect automatically re-adapts to the new table size each time the file is re-uploaded. This is useful for time series or if you are gradually adding more lines or columns over time (with more metrics or more periods), for example.
- Indicate if the table has a header line and/or column. Header lines and columns will be added to the sides of the preview table and remain visible while you navigate during checking the data.
Step 4 – Tag the Lines and Columns that Carry Key Datapoint Parameters
By default, every line and column is set to “Data”. Use the header dropdowns to identify what information each line and column contains.
The dropdown offers you all the required defining parameters of a datapoint:
- Field Mnemonic
- As-of Date
If you tag a line with “Ticker”, you are indicating that all cells in that line carry symbols (eg: Sentieo Tickers, Custom Entities or Custom Quotes, or any other identifiers we recognize such as ISIN, CUSIP, BBG ticker, etc..).
If you tag a line or a column with “Field Mnemonic”, you are indicating that all the cells in that line or column carry ticker codes previously created by your organization on Sentieo Connect. These Fields are those that relate to the datapoints in the adjacent lines and columns respectively.
Likewise, if you tag a line or a column with “Period”, you are indicating that all the cells in that line or column carry Periods (eg: FY2021, March-09, etc.) which are related to the adjacent datapoints in that table.
The same applies to Scenarios, As of Dates, and Sources.
Step 5 – Tag Data Rows and Columns without Headers with Field Codes Directly
You can also find all the Custom field codes created in your organization in the dropdown. Use these on a line or column if all the data in that line or column is for a given field and the name of that field is not explicitly indicated in the header line or column.
Step 3 to 5-bis: If the data is in more than one table
Use the top Table pane to add more tables to your mapping and follow the area targetting steps above for each table.
Step 6 – Review the fallbacks
Every datapoint needs the following parameters:
- A target Entity
- A Field
- A Period (for periodic fields)
- An Observation Date
- A Value, valid according to the field nature (Eg: number for number)
- A Scenario
- A Source
So for any datapoint in the table that does not have all of this information, Sentieo Connect will rely on the fallback values you indicate in this section to fill in the gaps.
In the example used in this walkthrough, only Ticker and As of Date are present in the table, so fallbacks are used for everything else.
Types of fallbacks and available options
For each time of fallback, you can either use a fallback value or instruct Sentieo Connect to fail the datapoint, in one of 3 ways:
- Assume a Ticker: lets you indicate a cell on the worksheet where to find the fallback ticker, or type it manually
- Error: If a datapoint is not clearly met with a ticker based on how the table is organized and mapped, then the datapoint counts as an error towards the threshold of acceptance of the overall sheet
- Fail the upload: use this option if you consider that a missing ticker for a datapoint is so crucial that fact alone should fail the overall workbook upload
- Ignore the datapoint: use this option if you consider that a missing ticker for a datapoint is a minor issue so that it does not even count towards the threshold of acceptance of the overall sheet
- Scenario: The same four options are available for Scenarios: Assume a Scenario, Error, Fail the upload, and Ignore the datapoint.
- Source: The same four options are available for Source: Assume a Scenario, Error, Fail the upload, and Ignore the datapoint.
- Field Code: The same four options are available for Field Codes: Assume a Scenario, Error, Fail the upload, and Ignore the datapoint.
- Observation Date: The same four options are available for the observation Date: Assume a Scenario, Error, Fail the upload, and Ignore the datapoint.
- Watchlist Action: You can optionally upload all the tickers of the table into a watchlist.
- “Replace a watchlist” completely replaces the existing watchlist with this new list of tickers.
- “Update the watchlists” adds all the new tickers identified in the table to the existing watchlist.
The Final Result
This table has a very simple structure and only needed to tag one column (Observation dates), and one line (Tickers) to cover the scope. However, it does not have all the required parameters, so we had to rely on the fallbacks.
In this case, it is assumed that the scenario for all the uploaded datapoints in this table was “base”, the Source was “LinkedIn”, the Field Code was “UD.Linked_In_Data”, and the watchlist (optional) to update with the ticker list was “LinkedIn Data Coverage List”.