Getting started

The following instructions are summarized and provide you with a basic understanding of the topic. Please contact our support team with any questions.

Economatica's “Add-in for Excel” is an application that allows you to access the Economatica server database directly through Excel (without using the proprietary interface of the Economatica system).

Initially click on the icon below to download the Economatica’s "Add in for Excel" application.

 

 

Then run this program.

After executing it your Excel should pass to display a tab called "Economatica".

 

In order for your Excel to be able to access the Economatica servers you will need to log in. The log in will be done through your Excel. Click the "Log in" button that is located in the "Economatica" tab of your Excel.

Log in using the same email and password you use to access the proprietary interface of the Economatica system.

 

 

You must edit functions in Excel cells so that they display data extracted from Economatica servers.

Example of a function: = economatica ("msft"; "close" ;; "2017-11-20").

If you fill a cell with the above function this cell will now present Microsoft’s (MSFT) closing price on 11/20/2017.
There are 3 ways you can create functions in Excel:

  • By copying a screen of the Economatica system (easier, recommended);
  • Using the Excel function editor;
  • Typing directly into the Excel cell itself (as in the example above).

Create functions by copying a screen of the Economatica system

This is the most recommended because it is the simplest alternative to creating functions in your Excel.

Initially you should open the Economatica system, that is, access the proprietary interface of the Economatica system. Please contact our support if you do not know how to operate the proprietary interface of the Economatica system.

Once in the Economatica system you should open the Screening window or the Matrixx window and create columns containing the data you want to have in Excel.

Then you have to highlight the desired data, right click on the highlighted area and choose the option "Export to Excel add in".

 

Then paste this content into your Excel using CTRL + V.

When doing this you will be pasting in Excel not the data itself but the functions that will allow your Excel to communicate with the Economatica servers and fill the cells with the desired data.

After doing this operation (and saving your Excel spreadsheet) you will no longer need to use the proprietary interface of the Economatica system.

Create functions using Excel function editor

Click on the Excel function icon (fx), select the "Economatica" category and then double-click on the "Economatica" function.

 

Excel will then offer its function editing screen. The following sections contain the instructions to complete each of the fields you can bring into Excel.

Filling the field "TICKER"

In the field "Ticker" you must enter the code of the security whose information you want.

For unlisted securities (funds, etc.) enter the same code used in the proprietary interface of the Economatica system.

The ticker field can be filled in 3 different ways:

  • Enter a ticker. Example: "MSFT" (between double quotation marks);
  • Fill in with the coordinates of a cell where you previously entered the desired ticker. Example: A3 (without quotation marks);
  • Place a range of cells where you previously entered the various securities’ tickers for which you want the information. Example: A3: A53 (without quotation marks).

Filling the field "ATTRIBUTE"

In the field "Attribute" you should indicate the information you want. Examples: closing price, traded volume, volatility, return, P / L, sector, net earnings, net revenue, ROE, EBITDA margin, and all the other hundreds of possibilities that are offered in the proprietary interface of the Economatica system.

There are two ways to fill the "Attribute" field :

  • Choosing the attribute from a list (recommended, easier)
  • Entering the attribute name

Choosing the attribute from a list

Place the cursor on the "Attribute" field and click on the icon highlighted in the image below to access the list of all available attributes.

 


You will then access a screen similar to the attribute selection screen of the proprietary interface of the Economatica system. Please contact our support team if you have questions about navigating this screen.

Entering the attribute name

The exact name that should be used is the one that appears in the columns of the Screening window of the ENGLISH version of the Economatica system (proprietary interface).

 

 

Therefore, to know the exact names to be used in the "Attribute" field, you must follow the steps below:

  • Connect the proprietary interface of the Economatica system;
  • Switch to English
  • Open a Screening window; 
  • Create a column with the desired information;
  • Note the name used in the column header (name only, not including parameters).

Please contact our support team if you do not know how to operate the proprietary interface of the Economatica system.

Filling the field "ATTRIBUTE´S PERIOD"

Depending on the attribute chosen in the previous field, you must enter the desired period. Example: the attribute "Return" can be "in the year", "in 1 month", "since 05/05/2015", "since the beginning of the series", etc.

Several attributes are not associated with a period however and for these this field should be left blank. Examples: closing, stockholder’s equity, sector, etc. The period should be written in double quotation marks, in English, and must adhere to the pattern of the examples below.

For items like return, volatility, etc., the options are:

  • "1d", "21d", "52w", "5y", etc;
  • "in the week", "in the month", "in the quarter", "in the year";
  • "from start" (from the beginning of the price series);
  • "yyyy-mm-dd" (since a specific date);
  • A5 (provide the coordinates of the cell where you previously entered the desired period).

For items such as net earnings, EBITDA margin, etc., the options are:

  • "3m", "12m", "in fiscal year" (since the beginning of the fiscal year of the company);
  • A5 (provide the coordinates of the cell where you previously entered the desired period).

Filling the field "DATE"

In this field you will indicate the date for which you want the information chosen in the "Attribute" field. Example: the closing quote on 5/5/2015 or on 12/31/2017 or today or a month ago, etc.

Several attributes are not associated with a date however and for these this field should be left blank. Examples: sector, name of fund manager, etc.

The date should be written in double quotation marks, in English and must adhere to the pattern of the examples below.

For daily items such as closing, volatility, P / L, etc. the options are:

  • "yyyy-mm-dd" (on a specific date);
  • "D-0" (on the last day already closed), "D-1" (one day before the last day already closed), "D-2", etc.;
  • "D-1M" (one month before the last day already closed), "D-2Y" (two years before the last day already closed), "D-52W", etc.;
  • “latest” (the last day in which this security was traded. This date will be different from D-0 only for illiquid securities that did not trade on D-0);
  • A5 (provide the coordinates of the cell where you previously entered the desired date).

For quarterly items such as net earnings, EBITDA margin, stockholder’s equity, etc., the options are:

  • "yyyy-mm-dd" (on a specific date);
  • "latest" (from the latest financial statement available for that company);
  • "Jan / yyyy", "Feb / yyyy", etc. (of the financial statement with date in the specified month);
  • "<Mar / yyyy" (from the financial statement dated between 8 / Jan and 7 / Apr) (*);
  • "<Jun / yyyy" (from the financial statement dated between April 8 to July 7) (*);
  • "<Sep / yyyy" (from the financial statement dated between 8 / Jul and 7 / Oct) (*);
  • "<Dec / yyyy" (from the financial statement dated between 8 / Oct and 7 / Jan) (*);
  • A5 (provide the coordinates of the cell where you previously entered the desired date).

(*) These 4 options (with "<" sign before the name of the month) are useful when working with companies with irregular fiscal years, i.e., publish statements on dates other than 31/Mar, 30/Jun, 30/Sep and 31/Dec.

Filling the field "RANGE START DATE "

This field should be populated only when you want to get information for a range of dates and not just for a single date.

In this field you must enter the start date of the desired series. The end of the series will be the date you previously specified in the "Date" field.

The options are:

  • "yyyy-mm-dd" (since a specific date);
  • "20d" (20 days before the end date specified in the "Date" field), 52w, 2y, etc.;
  • A5 (provide the coordinates of the cell where you previously entered the desired date).

Filling the field "INTERVAL"

This field should be populated only when you want to get information for a range of dates and not just for a single date.

The options are:

  • "d" (display values every day within the chosen date range);
  • "w" (show only the values of the Friday of the weeks within the chosen date range and in case of cumulative values (*) show the accumulated values in the week);
  • "m" (show only the values of the last day of the months within the chosen date range and in the case of cumulative values (*) show the accumulated values in the month);
  • "q" (show only the values of the last day of the quarters within the chosen date range and in the case of cumulative values (*) show the accumulated values in the quarter);
  • "y" (show only the values of the last day of the year within the chosen date range and in the case of cumulative values (*) show the accumulated values in the year);
  • A5 (provide the coordinates of the cell where you previously entered the letter ("d", "w"," m", "q", or "y") representing the desired interval.

(*) cumulative values are those associated with a period and a not a single date. Examples: traded volume for a period, fund inflows for the period, maximum quote for the period, etc.

Filling the field "CURRENCY "

The Economatica database covers several countries so there is data in several different currencies (quotes, financial statements, etc.). This field allows you to convert the data to other currencies.

The options are:

  • "USD" (converts to US dollars);
  • "EUR" (converts to Euros, not available for some currencies);
  • "Inflation adjusted" (shows values in original currency but updated by inflation, not available for some currencies);
  • A5 (provide the coordinates of the cell where you previously entered the desired currency).

Filling the field "MULTIPLIER"

Use this field to define whether the values should be presented in thousands, millions, etc.

This field should be filled only for items (attributes) where the multiplier makes sense. Examples: Total Assets, Revenue, Volume traded, etc.

This field should not be filled for items (attributes) such as Closing Price, Net margin, Earnings per share, etc.

The options are:

  • "UNITS" (apply no multiplier)
  • "THOUSANDS"
  • "MILLION"
  • "BILLIONS"
  • "DECIMAL" (some attributes like Return, Volatility, etc are brought in from Economatica expressed in percentage (ex 12.7). If your preference is to display the values in decimal form then use the option "DECIMAL". This option will divide the original values by 100 and consequently display the numbers in decimals (ex 0.127))

Filling the field "SHOW DATES"

In cases where you have defined a function that brings values for a date range, a column with the dates will appear.

In some situations you may prefer to omit this column of dates. For example when you want to place multiple columns side-by-side (Example: closing quote, P/L, volatility, etc.) it will be more convenient that the first function on the left shows your date column and the other functions omit the dates.

To omit the date column, fill in this field with "FALSE".

Filling the field "SHOW HEADER"

The Header is the description that appears in the top cell.

Example: "Return in 1 year", "Earnings of 3 months", etc.

In the functions of a single ticker and a single date the header does not appear (by default). The header however appears (by default) in the two situations described below:

  • When you set a function for multiple tickers;
  • When you set a function that brings a range of dates;

In some situations you may prefer to omit the header. For example when you are embedding Economatica data into a report that already has a defined design.

To omit the header, fill in this field with "FALSE".

Filling the field "CUSTOMIZED HEADER"

Use this field to choose a header different from the default used by the system. Enter the desired header in this field.

Filling the field "OPTIONALS"

Through the "Optionals" field you can define other miscellaneous settings. Examples:

  • Show historical prices not adjusted by corporate actions (by default prices are always presented adjusted by corporate actions);
  • Choose a benchmark for the Sharpe Index other than the default benchmark
  • etc.

The syntax of this field is complex. Please contact our support team for instructions on how to create the syntax for this field.

Create functions directly in an Excel cell

The syntax of the functions to be entered in Excel cells should follow the sequence below:

= economatica ("ticker","attribute", attribute's period","date","range start date","interval","currency","multiplier","show dates","show header","customized header","optionals") .

The syntax above represents the case where all fields are used. Most cases however will use only a few fields.

Before going on to the examples below please note the following remarks:

  • The permitted values for each field are those detailed earlier in this document.
  • The fields must be separated by the character that your Windows uses as "List Separator". If you do not know which character your Windows uses as the list separator, you can use the following rule as a guide (this rule is not valid if you have changed your Windows default settings) : Fields must be separated with a comma when Excel uses the dot as the decimal separator. While the fields must be separated by semicolons when Excel uses the comma as the decimal separator. In the following examples the fields are separated by commas:

 

Example 1) Display Microsoft's Industry Sector (MSFT) according to the NAICS classification. In this case only the first two fields will be used.

= economatica ("msft", "sector naics")

 

Example 2) Show Google's (GOOG) closing price on April 5, 2018. In the case of the closing price the attribute's period field is not used, so an empty space must be left in the reserved position for this field (this is the empty space that is between two commas).

= economatica ("goog", "close",, "2018-04-05")

 

Example 3) Show Microsoft's stock (MSFT) return from the beginning of the year to the most recent date.

= economatica ("msft", "return", "in the year", "d-0")

 

Example 4) Show Google's (GOOG) net earnings in each quarter in the last 4 years (this will be 16 quarters) to the last available, on a quarterly date scale and converted to Euros.

= economatica ("goog", "net income", "3m", "latest", "4y", "q", "eur")

 

Example 5) Show the maximum price from the last 52 weeks for a list of 20 tickers (which you previously placed between cells A5 and A24). Show inflation-adjusted data. Omit the header. In this example the fields "range start date", "interval", "multiplier" and "show dates" will not be filled so you must leave empty spaces in the reserved positions for each of these fields.

= economatica (A5: A24, "high", "52w", "d-0" ,,, "inflation adjusted",,, "false")