/xlFOCUS

Fetch Brazil economic data to Excel from webservices, including FOCUS (market expectations); SGS, ipeadata, and IBGE (economic indicators); SCR (credit data); SPI (payments system); and others.

Primary LanguageVBA

xlFOCUS (v. 0.6)

This spreadsheet exposes examples of how to fetch Brazil economic data from webservices to Microsoft Excel (Windows only), including FOCUS (market expectations); SGS, ipeadata, and IBGE (economic indicators); SCR (credit data); SPI (payments system); and more. It is intended to be used by researchers and the general public. It is NOT a product of the Brazilian Central Bank nor of any other government institution. Use at your own risk! It is totally free and its code is open!

TIPS

  • Most recent version should be found on this webpage: https://github.com/edugca/xlFOCUS

  • To update from a previous version, just replace the old version of this spreadsheet with this one. To update in your own spreadsheets, you need to open each one of them in the VBA Editor and replace the module "xlFOCUS" with its current version embedded in this spreadsheet. If you are updating from a version older than v. 0.4, you need to also replace the module "JsonConverter" by the current version of the module "xlFOCUS_JsonConverter" also embedded in this spreadsheet. If you are updating from a version as old as at least v. 0.4, you need to also replace the module "xlFOCUS_JsonConverter" by its current version also embedded in this spreadsheet.

  • To use these functions in your own spreadsheet, use this spreadsheet as a model, it is EASIER! Alternatively, copy to your spreadsheet the VBA modules named xlFOCUS and xlFOCUS_JsonConverter, both embedded in this spreadsheet. The JsonConverter module is part of the "VBA-JSON" project developed by Tim Hall and available at the website below. The xlFOCUS_JsonConverter module is a slight modification of v2.3.1. so as to simplify its use in this tool.

https://github.com/VBA-tools/VBA-JSON

  1. With your spreadsheet open, make sure macros are enabled and the Developer tab is displayed. In the links below, you learn how to do that:

https://support.microsoft.com/en-us/office/enable-or-disable-macros-in-office-files-12b036fd-d140-4e74-b45e-16fed1a7e5c6 https://support.microsoft.com/en-us/topic/show-the-developer-tab-e1192344-5e56-4d45-931b-e5fd9bea2d45

  1. In the ribbon, click on Developer tab, then on "Visual Basic".

  2. Drag the VBA modules "xlFOCUS" and "xlFOCUS_JsonConverter" from this spreadsheet to your spreadsheet. Now, close the VBA Editor. You're ready to use xlFOCUS!

  • These functions rely on fetching data from online webservices, which can be unavailable at times. So, when designing your spreadsheet, consider copying function results and pasting them as values.

  • There should be a function for each resource covered by this tool. One can find the metadata on their webpages:

BCB FOCUS https://olinda.bcb.gov.br/olinda/servico/Expectativas/versao/v1/aplicacao#!/recursos

BCB SCR https://olinda.bcb.gov.br/olinda/servico/taxaJuros/versao/v2/aplicacao#!/recursos

BCB SGS https://dadosabertos.bcb.gov.br/dataset/20542-saldo-da-carteira-de-credito-com-recursos-livres---total/resource/6e2b0c97-afab-4790-b8aa-b9542923cf88

BCB SPI https://olinda.bcb.gov.br/olinda/servico/SPI/versao/v1/aplicacao#!/recursos

BCB MercadoImobiliario https://dadosabertos.bcb.gov.br/dataset/informacoes-do-mercado-imobiliario

IPEA ipeadata http://www.ipeadata.gov.br/api/

IBGE IBGE Agregado https://servicodados.ibge.gov.br/api/docs/agregados?versao=3

IBGE IBGE SIDRA https://apisidra.ibge.gov.br/

  • If your query is not working, try to build it on the webpage above. If the server is down, then all functions will fail to fetch data!

  • Beware that the BCB's webservices are case sensitive, that is, "IPCA" works but "ipca" does not.

  • To read large JSON files generated by the webservices (tested with FOCUS, SCR and SPI), download them to a folder and then read them with the function xlFOCUS_ReadJSONFile.

  • There are some known limitations with functions that directly query the webservices. Basically, avoid returning much data in a single function call and avoid many function calls:

    • 10,000 is the maximum number of observations returned by each function call
    • In general, the limitations of each webservice also apply to the queries demanded by this tool's functions. We recommend you to read their documentation.

Version history

v 0.1 (2021-12-16)

  • First release.

v 0.2 (2021-12-19)

  • More instructions on how to implement these resources on the user's spreadsheet.
  • New function to read JSON files: xlFOCUS_ReadJSONFile.

v 0.3 (2021-12-26)

  • Fixed the encoding of the text read by the function xlFOCUS_ReadJSONFile. Now, it is correctly set to UTF-8.
  • New function to get data from the SGS system: xlFOCUS_SGS.
  • New function to read JSON script returned from the SGS system: xlFOCUS_SGS_ReadJSON.
  • New function to get data from the SCR system: xlFOCUS_SCR_TaxasDeJurosDiario.

v 0.4 (2022-01-01)

  • Major change: instead of using Excel's WEBSERVICE function to fetch data, xlFOCUS now uses the object MSXML2.serverXMLHTTP, which allows for larger query results to be fetched!
  • All references to non-standard libraries in the VBA code are now late-binding, so there is no need to make reference to them in the user's spreadsheets.
  • New function to fetch data from any webservice: xlFOCUS_WEBSERVICE. It does not suffer from the same data amount limitations imposed by Excel's function WEBSERVICE, but it is still limited by Excel's maximum string size.
  • New function to read a JSON script from BCB's webservice: xlFOCUS_ReadJSONFromWEBSERVICE. It does not suffer from neither the same data amount limitations imposed by Excel's function WEBSERVICE nor from Excel's maximum string size.
  • Function xlFOCUS_SGS_ReadJSON now returns N/A for missing observations.
  • New function to get time series data from the ipeadata webservice: xlFOCUS_ipeadata.
  • New function to get the metadata from a time series from the ipeadata webservice: xlFOCUS_ipeadata_Metadados.
  • New function to get JSON data from the ipeadata webservice: xlFOCUS_ipeadata_ReadJSON.
  • New function to get JSON data from the MercadoImobiliario webservice of the BCB: xlFOCUS_MercadoImobiliario.
  • New function to get data from the SCR system: xlFOCUS_SCR_TaxasDeJurosMensal.

v 0.4.1 (2022-01-01)

  • In the presence of old dates (< 1900-01-01), ipeadata functions return dates formatted as text 'yyyy-MM-dd'.

v 0.5 (2022-01-13)

  • Functions xlFOCUS_ReadJSON and xlFOCUS_ReadJSONFile have got a new optional parameter called "subfield". It allows to climb down 1 step in the JSON structure before building the returned table. Previously, these functions automatically descended the field "value" as this is the case of scripts returned by the BCB's webservices, but not of IBGE's webservice, for example, which now xlFOCUS is also compatible with. In reason of that, calls to old versions of these functions must now make explicit reference to the "value" argument in the subField parameter. Check the examples available in this spreadsheet in case of doubt.
  • New functions to get time series data from the IBGE webservices: xlFOCUS_IBGE_Agregados and xlFOCUS_IBGE_SIDRA.
  • Minor fixes to function xlFOCUS_SGS.

v 0.6 (2022-08-25)

  • New function to extract the nth element of a vector: xlFOCUS_INDEX. It allows negative positions, which are counted from the last element to the first one.
  • New function xlFOCUS_ExpectativasMercadoSelic for fetching Selic forecasts for specific Copom meetings.