:
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 61 Next »

back-to-top

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)
  • 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;
  • "YTD" (year to date), "QTD", "MTD", "WTD";
  • "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)

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 (according to their ISO codes).

Example:

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

As new securities are regularly added to 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

Introduction

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, there is a sector category called "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"}

Other operators for OPTIONALS field

The filters for OPTIONALS support other operators in addition to "equal to" (=) and "not equal to" (<>)

 

Example 1) Company name CONTAINS the word "Health"

"name~health"

 

Example 2) Company name DOES NOT CONTAIN the word "Health"

"name!~health"

 

Example 3) The share's ISIN code is available, ie. the ISIN field IS NOT NULL

"ISIN<>null"

Filtering by configurable variables

It is possible to make filters using variables that are configurable. For example, it is possible to filter shares of companies whose Total Asset is greater than 10,000,000,000

"Total assets>10000000000"

When performing the above filter the system will take the default values attributed to the Total Assets, that is, the value from the company´s most recent financial statement [DATE = LATEST] and its value will be expressed in the currency in which the statement was originally published [CURRENCY = ORIGINAL CURRENCY].

You can however change these parameters as explained below

 

Example 1) Get the value of Total Assets from the 12/31/2018 statement

Total Assets[DATE=2018-12-31]>10000000000



Example 2) Get the value of Total Assets in Euros

Total Assets[CURRENCY=EUR]>10000000000



Example 3) Get the value of Total Assets from the 12/31/2018 statement And in Euros

Total Assets[DATE=2018-12-31,CURRENCY=EUR]>10000000000

It is recommended to place the filters in separate cells

If you use complex filters, we advise to place them in separate cells so that it will be possible to view the filters more clearly and avoid errors

Suppose you want to filter the shares of companies that meet the following filters:

Sector (Economatica Classification) =Construction
Total Assets in 2018-12-31 > 10.000.000.000
ROE > 10 in 2017, 2018 and 2019

If you do not place these filters in separate cells you will have a convoluted situation as shown below

 

 

However, when placing the filters in the selected cells, as the image below demonstrates, you’ll have a more organized and less error-prone situation.

Filters must be placed in adjacent cells (vertically or horizontally)

 

 

Function ECOPORTFOLIO (used to add holdings to create a portfolio)

Introduction

Via the ECOPORTFOLIO function you can define the composition of a portfolio containing any asset that is available in the Economatica database: stocks, ETFs, funds, etc. (depends on the database you contracted)

After inserting the composition of the portfolio using the ECOPORTFOLIO function, you will use the function ECONOMATICA to obtain data such as:

 

  • historical series of the share value of this portfolio
  • portfolio annual return
  • portfolio volatility
  • graph the evolution of the portfolio's value
  • compare the performance of the portfolio with the performance of a benchmark
  • etc


There are two different types of portfolios. The difference between them is the type of information used to define the portfolio. You must choose the type that is compatible with the information you have about the holdings. The two types are:

 

  • Percentage Portfolio: inform the percentage weights of each asset on one (or more) date
  • Financial Portfolio: inform each buy and sale transaction

Percentage Portfolio Features

To define a Percentage portfolio, the user must inform the percentage weights of each asset on a certain date, following the format of the example below

Do not use the “%” symbol

It is also possible to inform the percentage weights of each asset on more than one date



The historical NAV series of this portfolio will be created.

Its start date will coincide with the beginning of the historical series of the youngest holding in the portfolio, that is, whose historical series has started more recently.

Example: If one of the holdings of the portfolio is a stock whose trading started just 20 days ago, then the NAV series of the portfolio will also start 20 days ago

The initial NAV value will be 100

When the user provides the weights for only one date, the composition of that date will be valid for previous dates (from the beginning) and will be valid for later dates (until today)

When the user informs the weights for more than one date, each composition defined will be valid for the subsequent dates until the next defined composition (or until today in case it is the last composition defined by the user). The first user-defined composition will also apply to previous dates (from the beginning).

For Percentage Portfolios (not applicable to Financial Portfolios) the user will be able to choose between rebalancing or not rebalancing the composition. See details in the chapter "Filling in the field REBALANCE"

Financial Portfolio Characteristics

To build a Financial portfolio, the user must inform the buy and sale transactions according to the format of the example below.

The historical NAV series of the portfolio will be created.

Its start date will coincide with the date of the first transaction

The initial NAV value will be 100

In the Financial portfolio (not applicable to the Percentage portfolio) in addition to the NAV series, the series of portfolio asset values will also be created.

The following are guidelines on how to complete each field of the ECOPORTFOLIO function

Filling in the field TYPE OF PORTFOLIO

This field must be filled with P or F if you want a Percentage portfolio or a Financial portfolio respectively. See explanations of this subject above in this chapter.

Filling in the field COMPOSITION / TRANSACTIONS

In this field you must indicate the cell range where you previously entered the weights or transactions of the portfolio.

To build a Percentage portfolio, as explained previously, you must inform the percentage weights of each holding on one (or more) date following the format shown in the image below. In the case of the example below, this field should be filled with B3: E7

Do not use the “%” symbol

To build a Financial portfolio, as explained above, you must inform the buy and sale transactions in the format shown in the image below. In the case of the example below, this field should be filled with A4: C11

Filling in the field TOLERANCE

If any holding in the portfolio does not have a price on one or more dates, enter the maximum period that the previous price for that holding should be repeated. Ex : 5d, 1m, etc

If even after considering the tolerance chosen by the user, any component still has dates without prices, then the portfolio will not be accounted on those dates.

Filling in the field REBALANCE

This field should only be filled in for Percentage portfolios (does not apply to Financial portfolios).

The options are N (no) and Y (yes). If this field is left blank, option N

N : Don't-Rebalance. On the dates after (*) the composition informed by the user, the composition of the portfolio will show changes in relation to the initial weights. These changes are caused by the fact that the portfolio holdings have different valuations over time

Y : Rebalance. On the dates after (*) the composition informed by the user, the composition of the portfolio will remain unchanged in relation to the initial weights. The composition will remain unchanged (despite the fact that the portfolio holdings have different valuations over time) because the system will create a scenario equivalent to executing daily buy and sale operations necessary to keep the initial weights unchanged.

(*) It will also be valid for previous dates if that composition is the only one (or the first of several) informed by the user

Filling in the field NAME

Fill in this field with the name you want to assign to the portfolio

Function ECOBENCHMARK (used to create a benchmark)

Introduction

Via the ECOBENCHMARK function you can create a benchmark based on any security available in the Economatica database (depends on the database you contracted)

A price series will be created for this benchmark so that your returns are equal to the returns of the base security but amplified by a factor chosen by the user

Ex: S&P 500 + 6%, CPI-U + 4%, 110% of T-Bond, etc

This amplification of the returns on the base security can be done by adding a factor (S&P500 + 6%) or by multiplying by a factor (110% of the T-bond)

Example of the calculations involved in the creation of a benchmark that adds a factor: Consider a year in which the variation in the base security has been 5%. If the user chooses a factor of 6, the benchmark variation will be 11.3% this year:

11.3% = (1.05 * 1.06 - 1) * 100

Example of the calculations involved in the creation of a benchmark that multiplies by a factor: Consider a day when the change in the base security has been 0.02%. If the user chooses a factor equal to 110, the variation of the benchmark will be 0.022% on this day:

0.022% = 0.02% * 1.1

NOTE: The price series of the benchmark is constructed in such a way that the DAILY variations of the benchmark are, in this example, 110% of the DAILY variations of the base security. A mathematical consequence of this is that the ANNUAL variations of the benchmark will only be approximately equal (and not exactly equal) to 110% of the ANNUAL variations of the base security.

The start date of the benchmark series will coincide with the start date of the series of the base security.

On the final date the value of the benchmark series will be 100

The following are guidelines on how to complete each field of the ECOBENCHMARK function

Filling in the field SECURITY

Enter in this field the code (as used in the Economatica system) to be used as the base security for the benchmark. Ex: "CPI-U", "S&P 500", etc.

Filling in the field TYPE OF OPERATION

Fill this field with "A" to add a factor (Ex: S&P 500 + 6%) and "M" to multiply by a factor (Ex: 110% of T-bond). See explanations of this subject above in this chapter.

Filling in the field FACTOR

In this field you must enter the value of the factor. Ex: 5, 110. Do not use the "%" symbol

Filling in the field NAME

Enter the name you wish to assign to the benchmark

 

Create functions directly in an Excel cell

As we know, Excel lets you type a function in the cell itself without opening the function edit screen. To do so, you must follow the syntax below and include the fields in the same order as they are shown in the function edit screen.

  • = 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 same to all the other functions

The examples above present 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:

 

Example 1 - Function ECONOMATICA) 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 - Function ECONOMATICA) 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 - Function ECONOMATICA) Show Microsoft's stock (MSFT) return from the beginning of the year to the most recent date.

= economatica ("msft", "return", "YTD", "d-0")

 

Example 4 - Function ECONOMATICA) 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 - Function ECONOMATICA) 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")

 

Example 6 - Function ECOSECURITIES) 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 7 - Function ECOSECURITIES) Select all the government securities issued by Brazil that are currently active (in other words, exclude those cancelled)

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

 

Example 8 - Function ECOSECURITIES) Select the stocks of the companies whose sector is construction (using the Economatica classification).

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

 

Example 9 - Function ECOSECURITIES) Select funds from Brazil

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

 

Example 10 - Function ECOSECURITIES) Select funds from Brazil and Mexico (the two countries should be separated by commas)

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

 

Example 11 - Function ECOSECURITIES) 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

The alternative below is only available for the ECONOMATICA function (not available for the ECOSECURITIES function or others).

It's possible to create a function ECONOMATICA 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.

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.

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.

 

 

Update status of your spreadsheet data

The icon below may appear on your spreadsheet toolbar

 

The displaying of this icon does not necessarily indicate a problem. See below the description of its meaning.

Economatica's server is constantly being updated with new data. In addition to daily quotes, huge amounts of information is entered into the database: new company balance sheets, new fund portfolio holdings, master data, historical data corrections, etc.

Approximately every 5 minutes (this interval may vary greatly) the server is loaded with new data. That is, approximately every 5 minutes the server database instance changes.

This icon indicates that some cells in your spreadsheet were updated at a certain time while new functions you created later were updated at a time when the server had already been fed with new information and therefore the database was in a new instance.

This icon may also appear if your spreadsheet is very large and all functions are slow to update and a server update occurs while your spreadsheet is still refreshing.

This is RARELY a problem since it is very unlikely that there will be any conflict between your spreadsheet data caused by the fact that different cells are reflecting different database instances.

However, clicking on this icon will update all functions in your spreadsheet and will be synchronized with the same moment as the principal server database.

 

  • No labels