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.

For each specific task there is a different function as described below

  • Function ECONOMATICA (returns a data item or a historic series)
  • Function ECOSECURITIES (returns a list of securities) (This function will be released before August 31st, 2018)
  • Other functions to be added in the future

There are 3 ways you can create functions in Excel:

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

Below we describe how to create each of these functions using the Excel function editor. Further below in this document you will find instructions on the two alternatives (type directly into an Excel cell or copy from a screen in the Economatica system)

To access the Excel function editor click the Excel function icon (image 1), select the "Economatica" category (image 2) and then double-click on the desired function :ECONOMATICA, ECOSECURITIES, etc (image 3)

 

 

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.

Function ECONOMATICA (returns a data point or a historic series)

Introduction

Through the function ECONOMATICA the user can obtain two types of content:

  • A data item (a price quote from a certain date, or earnings from a certain date, or the company sector, or the name of a fund manager, etc)
  • A historical series for a data item (historical series of prices for a selected period, historical series of earnings for a selected period, etc)

Below are the instructions on filling each of the fields for the function ECONOMATICA in order to obtain the desired data.

Not all the fields need to be filled. You should fill only the fields that are necessary and sufficient to define the desired data.

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.

Function ECOSECURITIES (returns a list of securities)

(This function will be released before August 31st, 2018)

Introduction

Use the function ECOSECURITIES to define filters to get a list of certain securities.

Examples: the list of the stocks traded in the NASDAQ Stock Exchange, the list of the funds in Brazil, etc.

The list will consist of the securities codes and their respective exchange/source.

Example:

  • AAPL<XNAS>
  • MSFT<XNAS>
  • AMZN<XNAS>
  • AMXL<XMEX>
  • COPEC<XSGO>
  • VALE3<XBSP>
  • ITUB4<XBSP>

As new securities are regularly added of the database (shares issuances, etc), each time that the function ECOSECURITIES is updated/processed may result in changes to the list.

Below are the instructions on filling each field (filter) of the function ECOSECURITIES to obtain the desired security list.

Not all the fields (filters) need to be filled. You should fill only the fields that are necessary and sufficient to define the desired security list.

Filling the field TYPE OF ASSET

This field is used to indicate which type(s) of asset represents the list you're interested in. The options are:

  • "STOCK"
  • "FUND"
  • "ETF"
  • "CORPORATEBOND"
  • "GOVERNMENTBOND"
  • "ADR"
  • "COMMODITY"
  • "CURRENCY"
  • "STOCKINDEX"
  • "INFLATION"
  • "FIXEDINCOMEINDEX"
  • and others

Filling the field ACTIVE/CANCELED

Choose whether you want securities that are still actively traded or securities that are no longer traded. The options are: "ACTIVE" and "CANCELED"

Filling the field SINGLE ENTRY

Companies that issue multiple asset classes (different share classes, etc) will show repeatedly on the list (per each asset class). Fill this field with "TRUE" so that each company shows up only

once on the list, represented by its most liquid asset class.

Filling the field COUNTRY

Indicate the country of origin for the companies whose securities you're interested in. The options are: "USA", "ARG", "BRA", "CHL", "COL", "MEX", "PER" and others

Filling the field EXCHANGE

Indicate the stock exchange where the securities you're interested in are traded. The options are the exchange identifiers according to their ISO codes:

  • "XNYS" (New York Stock Exchange)
  • "XNAS" (NASDAQ)
  • "XBUE" (Buenos Aires Stock Exchange)
  • "XBSP" (Sao Paulo Stock Exchange)
  • "XSGO" (Santiago Stock Exchange)
  • "XBOG" (Bogota Stock Exchange)
  • "XMEX" (Mexican Stock Exchange)
  • "XLIM" (Lima Stock Exchange)
  • and others

Filling the field SHOW HEADER

The cell on top of the list of securities will be filled with the header "Ticker"

In certain cases you might prefer to omit the header. For example if you're adding Economatica data to an existing report with a specific design.

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

Filling the field CUSTOMIZED HEADER

Through this field you can choose a different header from the system's default. Type in your own header in this field.

Filling the field OPTIONALS

Through this field you can specify filters using other variables than those discussed above. Below are brief explanations, you may contact Economatica support for more detailed information.

 

Example 1) Select the stocks of companies in the construction sector (according to the sector classification Economatica):

"Sector Economatica=Construction"

 

Example 2) Select the stocks of companies in the construction sector or in the telecommunications sector (the two sectors should be separated by comma)

"Sector Economatica=Construction,Telecommunication"

 

Example 3 - Problem caused by comma in the text) In certain cases the right side of the equation could contain a comma. For example, a category of the classification called "Segment Bovespa" is "Exploration, refining and distribution"

This text contains a comma between "exploration" and "refining". This comma will make the system think these are two separate categories. One being "Exploration" and the other "refining and distribution"

To solve this issue you have to insert the backslash ("\") before the comma

Segment Bovespa = Exploration\, refining and distribution

 

Example 4) Select funds whose Ambima classification is Multimercados balanceados:

"Anbima classification=Multimercados balanceados"

 

Example 5) Select funds whose Anbima classification is Multimercados balanceados AND whose fund manager is JP Morgan

To insert two or more filters it’s necessary to abide by the following syntax:

  • All the field content must be between braces
  • Each filter must be in quotes
  • The filters themselves must be separated by semicolon

{"Anbima classification=Multimercados balanceados";"Fund manager=JP Morgan"}

 

Example 6) It's possible to create a filter by typing into the cell:

Type in a cell (D5 for example) the desired filter ("sector Economatica=Construction" for example)

Fill the field OPTIONALS with "D5" (in other words, the reference to the cell where the filter is located)

Create functions directly in an Excel cell

Introduction

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")
  • = ecosecurities("type of asset","active/canceled","single entry","country","exchange","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.

Even if one or more fields are not used, it's necessary to maintain the proper syntax order as indicated above. Therefore, you must leave a blank space in the fields not used. Notice in the examples below how two consecutive commas (with no content in between) are found in the place of the unused fields.

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:

Function ECONOMATICA

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")

Function ECOSECURITIES

(This function will be released before August 31st, 2018)

Example 1) Select all the stocks traded in the NASDAQ exchange and that are currently active (in other words, exclude those cancelled)

=ecosecurities("stock","active",,,"xnas")

 

Example 2) Select all the government securities issued by Brazil that are currently active (in other words, exclude those cancelled)

=ecosecurities("governmentbond","active",,"bra")

 

Example 3) Select the stocks of the companies whose sector is construction (using the Economatica classification).

=ecosecurities("stock",,,,,,,"sector economatica=construction")

 

Example 4) Select funds from Brazil

=ecosecurities("fund",,,"bra")

 

Example 5) Select funds from Brazil and Mexico (the two countries should be separated by commas)

=ecosecurities("fund",,,"bra,mex")

 

Example 6) Select funds from Brazil whose Anbima classification is Multimercados balanceados and whose fund manager is JP Morgan

=ecosecurities("fund",,,"bra",,,,{"Anbima classification=Multimercados balanceados";"Fund manager=JP Morgan"})

Create functions by copying a screen of the Economatica system

Introduction

It's possible to create a function in Excel by copying from a screen in the Economatica system (proprietary interface) and pasting into 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.

Function ECONOMATICA

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.

Function ECOSECURITIES

It's not possible to create an ECOSECURITIES function in Excel by copying from the Economatica system

Proxy Configuration


Certain proxy configurations may prevent access to the Economatica Excel Add-in.

In these cases you will receive the message: "The server returned an error: (407) Proxy Authentication" (the exact text message may vary depending upon your environment).

To correct this problem you must use one of the following 2 options :

 

Option 1) Ask your IT department to unblock access to the following address:

 

Option 2) Enter the configuration settings of the proxy into a specific window found in the Economatica Excel Add-in. To access this window click on the icon "About / Help" shown below.

 

 

The configuration window will appear on the right-side of the screen. Complete the fields indicated in the image below. Ask your IT for the specific settings that are required.

Attention: The Economatica Excel Add-in does not currently support automatic proxy script.