1. Home
  2. Excel Plugin
  3. Getting Started With Sentieo Excel Plugin
  1. Home
  2. Microsoft Office Plugins
  3. Getting Started With Sentieo Excel Plugin

Getting Started With Sentieo Excel Plugin

You will find here a walkthrough to get started with creating formulas, accessing templates and leveraging on powerful tools such as the formula builder and the cloud syncing feature with Sentieo Document Search.

Excel Formula Builder

You can find the Formula Builder on the ribbon here:

Define or Finding a ticker

Type a ticker in the ticker search box (1); it will be automatically reflected into the output formula (2), so you can move to next step if you know this is a valid ticker;

If you are unsure or do not know the ticker, you can type the ticker or the name of the company (partially or in full) then hit Enter. (1). A new pop-up will offer all the listings we have with a Name or Ticker matching your search.

Here, you can:

  • (2) Change your search to something else
  • (3) Filter the results to only show Primary securities, Primary listings, and to exclude dead quotes.
  • (4) Sort the results by Name, Exchange, Ticker, Primary or Active Status
  • (5) Select one, multiple, or all results to be added to the formula
  • (6) Validate your selection or cancel

Define or search for a metric

You can look all our metrics by navigating through the tabs; High level, our metrics are sorted in 5 categories (1) (Financials, Estimate History, Trading Multiples, Market Data, Company Data), The number in the title (2) indicates the number of fields available in the category. the metrics from each category are grouped based on their nature, model and location you would naturally find them on app.sentieo.com (3). For example, you will find in the Financials tab a folder “Sentieo Model”, which has in order in which they appear all the field of the Sentieo Model from EDT.

if you type a field code or name (1), it will reflect automatically on the output formula (2), just like for the Ticker, but if you are unsure of the field name, you will notice that the field library section has now become filtered to only show field names or fields codes corresponding to the word you typed (3).

Press a field to select it and to reveal its description (4) and if it is periodic or daily and if it offers only historical content or information about future periods too;

Alternatively, you can also press Enter (1) after typing the field name/code to pop up a summary table of all the fields matching the search; Here, you can type another search (2), or sort the fields by name, code, category, or subcategory (3). Select any field and press Add to proceed.

Formula Configuration options

After selecting the field, fill out the remaining parameters and choose if you want to copy the formula to the clipboard, submit it to the current active Excel cell, or simply clear the Formula Builder to start over.

The configuration panel will dynamically change based on the field you selected so it only offers you parameters that are relevant. These parameters are different from a field to the other, and if the field is periodic or a daily time series, and if it has future periods or only historicals, and if it is basic information such as a company name or the business classification.

The basic options are

  • Formula type:
    • choose “Single Data Point” if you want to extract a single datapoint, or
    • “Time Series and Statistics” for a time series or a statistical calculation based on a time series (eg: Mean, Max, StdDev, etc..)
  • Series Selection (Estimate history series only): Estimate history series are specific to each period and come in multiple flavours (eg: Mean, Max, # contributors) so it is necessary to define both extra parameters (eg: the history of consensus revenue for the period FY2018);
    • Select Series Type: It is possible to choose from Mean consensus, Median consensus, Maximum Estimate in the sample of contributions, Minimum Estimate, Number of Contributors, or the Standard Deviation

    • Select the Estimated Period type, it is possible to select a specific fiscal period (eg: FY2018), or a sliding period ( eg: FY1 (Next Fiscal Year), LTG (Long Term Growth), or NTM (Next Twelve Months)).

  • Period Selection (Single Data point for Periodic Model series only): This helps identify the specific period we need a data point;

    • The Select Period Dropdown offers Absolute periods (eg: 1Q2015, C3Q2018, FY2018), Relative Periods ( eg: FY-5, FY0, CY2), and Sliding Periods (eg: LTM and NTM)
    • When Selecting LTM, LTM-Model or NTM, you can also indicate if you require the observation date to be a specific day in the past.

  • Reference Date (Single Data Point only): It is possible to define the reference point of the observation we need, based on:
    • a Date, or
    • a Fiscal or Calendar Period End Date

  • Date/Period (Time Series only): Select Timeframe: the boundaries of the time series can be defined either
    • in Absolute terms, by then providing a start and end dates (eg: From Jan 1 2015 to Dec 16, 2017) (not available for periodic series), or period (Eg from 1Q2015 to 4Q2018), or

    • in Relative terms, by specifying a time range (eg: 20 days) and a reference date or the end of the period for when the time series will end. (eg: 20 days up to Jan 15, 2020);
      The types of timeframe available includes days, weeks but also “to date” shorthands (eg: Week to Date  (WTD up to Jan 15, 2020).

  • Advanced Options
    • Frequency: Specify the sampling of the time series. Options available are: Daily (d), Weekly(w), Monthly (m), Quarterly, Half Yearly, Yearly.

    • Layout: Specify of the series should be laid out as a column ( Vertical) or on a line (Horizontal)

    • Statistic: It is possible to output a statistic based on the values at the boundaries of the time series; For example, =snt(“aapl”,”close”,”01/01/2018″,”01/01/2019″) will return the time series of the Apple stock close price between Jan 1 2018 and Jan 1 2019. =snt(“aapl”,”close”,”01/01/2018″,”01/01/2019″,”pct_chg”) will return instead only one value: the percentage difference between the value on Jan 1 2018 and Jan 1 2019. The statistics available are: Mean, Median, Absolute change, Percentage Change, CAGR, Minimum value of the time series, Maximum value, the Standard Deviation, the Total Sum, the Percentile rank of the latest value relative to the full time series, and the Normalized percentile rank of the latest value relative to the full time

    • Currency: select the currency in which you would like the results to be shown in;
      • for stock prices and Consensus Forecasts history, we assume that forecasts each day reflects the most recent FX moves so the conversion is made at the exchange rate of the day of each data point
      • for historical model metrics: the conversion is made at the spot exchange rate of the end of the period for Balance sheet metrics, and at the average exchange rate over the period for Income Statement and Cash Flow statement items.

    • Lookback (Single Data Point Estimate History Only): Estimate history time series have at least one data point every month in the unusual case where no contributor updated his forecasts to indicate that the estimates are still valid and coverage has not dropped; When calling a single data point from an estimate history series, and there is no value for that day, we look back by default up to 60 days for a value which we continue to assume to be valid; If you would like to change that you can specify a value in this parameter.

    • Fill Days: To make it easier to compare or plot two time-series, it is sometimes useful to “fill the gaps” by injecting dates for days without data points (eg: a stock trading in Dubai does not trade Fridays and it trades Sundays; putting the daily history of a NY traded stock and Dubai traded stock will use different dates which will not be matching side by side).
      • Trading Days – x: will produce a time series will all the trading days ;
      • Weekdays will have all weekdays
      • All Days will have all days of the year.

    • Show Column Headers: if unticked, the series will not display a label in the first cell
    • Show Dates: if unticked, the series will only have one column, without the support column with the corresponding dates.

Foreign Exchange Formula Builder

We have created a dedicated Formula Builder for streamlined foreign currency exchange. You can search for currency pairs by typing the name of the currencies, or the countries, or a mix of both.

After typing in the two currencies you wish to compare, the FX Formula Builder will present the most historically common pair first, with a preference for key global currencies and pairs with exchange rates greater than 1.

Template Library

Our template library offers many useful templates directly accessible from the Excel plugin. Feel free to use it directly in your models, or as a tool to quickly and easily learn how to build formulas with our plugin.

Basic Syntax Of Major Data Types

Please refer to the tearsheet below for a high-level overview on how to use the Sentieo excel formula =snt() with each type of dataset.

We also recommend to use the formula builder as a convenient interactive reference guide in order to learn or remember how to use the formula in need.

Quick model building with sntlines()

Sentieo offers multiple types of models and virtually any number from the web app is available on the Excel Plugin.
sntlines() formula comes in handy in two situations

Model building

While it is possible to type every formula one by one to replicate the models from EDT, we offer a dedicated formula to quickly map out and build any company model out of the Sentieo Standardized Model data or the Sentieo As Reported Model data (based on data from Refinitiv).

=sntlines(TICKER,[Model type or Field], [type of data])

  • Model type or field use a format SF.[Statement type].[Model Type]
    • Statement Type can be IS (Income statement), BS (Balance Sheet), or CF (Cash Flow Statement).
    • View Type: can be AS (As Reported), BSTD (Basic Standardized), or DSTD (Detailed Standardized)
  • Type of data can be: “name” (for the name of the line item, eg: Total Return), “fieldname” (for the name of the field itself; eg: SF.IS.TotalRevenue), or a fiscal period (eg: FY2016) to indicate the values for the given period are required.

Examples

    =sntlines(“aapl”,”SF.IS.AR”,”FY2016″) will return the values of all the lines of the As reported model for AAPL for the fiscal year 2016.
    =sntlines(“aapl”,”SF.IS.BSTD”,”FY2016″) will return the values of all the lines of a basic version of the Standardized model for AAPL for the fiscal year 2016. This basic version will show on degree of aggregation, but will not go all the way to highlighting every single component down to its source in the As reported model (eg: Total Revenue will only show that it is made of Revenue and Net Sales, but Net Sales will not show that it is made of Product Sales, Services Sales, and Net Sales for the years where Apple did not use to breakdown sales into Product and Services).
    =sntlines(“aapl”,”SF.IS.DSTD”,”FY2016″) will return all the lines of the advanced Standardized model for Apple; This will include further depth, to the point where items are directly sourced from the As Reported model (so the company financials).

=sntlines(“aapl”,”SF.IS.AR”,”fieldname”) will return the fields of each line of the As reported model in the right order.

=sntlines(“aapl”,”SF.CF.DSTD”,”name”) will return the name of the fields of the detailed standardized model.

The actual output of the formula is pre-made formulas, so it is possible to remove the initial =sntlines() formula, then move around the cells, add lines or columns to customize the initial output to your needs.

Find out a Sentieo Fundamental Standardized field’s individual components

sntlines() is not limited to the full model and can be used to call the breakdown of individual items. This is handy as a way to quickly audit the content of a field directly from within excel. Use =sntlines() and get as outputs its sub-components.

Examples

Apple’s Total SG&A expense (SF.IS.SGATotal) is made of

=sntlines(“AAPL”,”SF.IS.SGATotal”,”fieldname”)

Results:
SF.IS.LaborRelatedExp
SF.IS.SGAExp

Looking at Labor Related Expenses in more detail

=sntlines(“AAPL”,”SF.IS.LaborRelatedExp”,”name”)

Result:
Stock Based Compensation
Executive Bonus

Finally, the value for FY2016 of each of these components is

=sntlines(“AAPL”,”SF.IS.LaborRelatedExp”,”FY2016″)

Result:
0
1,552.00

Import from Sentieo App

Use the Sentieo’s Clipping feature to pull the custom datasets created on the Sentieo web platform. Seamlessly pull document tables and custom time series into your own models and worksheets without disrupting your flow. Here are a few ways to save tables into your cloud repository:

1 – Document Tables

2 – Time Series Output Screen

3 – Plotter Series

Save any derived plotter series here:

To download any of these datasets into Excel, click on the Sentieo Cloud button here:

Updated on January 13, 2020

Was this article helpful?

Related Articles