Data series from Brazilian Central Bank

Use BACEN web services to retrieve data directly from Excel

If you’re here, you probably know the Brazilian Central Bank — Banco Central do Brasil (BCB), or just BACEN — offers a web service where you can request data for specific series from its database. All information can be found in the Sistema Gerenciador de Séries Temporais (SGS) which has a massive set of data series. All available, free of charge.

Banco Central do Brasil captured from Google Street View in November 2015.

Through their API, you’ll find data series such as:

  • Broad National Consumer Price Index (IPCA);
  • General Price Index-Market (IGP-M);
  • Interest rates — CDI and Selic;
  • Anbima Market Index of the federal bonds tied to IPCA (IMA-B);
  • Exchange rate (USDBRL);
  • Brazilian stocks index (Ibovespa).

In summary, I’ll show you how to create an Excel function so you can get data without ever leaving Excel. We’ll be using CDI as an example, but you can adapt it to your needs. Now let’s go!

1. Install the digital certificate

To start, you’ll need to download and install the digital certificate from BCB. You can find it through the link: http://www.bcb.gov.br/?CERTDIG

Download the 2015 certificate chain…

… keep it …

… open it …

… and, finally, just install it by clicking next, next, and finish.

Need further help?❤ Hire me at codementor.io/fwuensche

2. Try the web service

If you just want to get into the results, jump ahead to step 3 below.

I recommend using Insomnia. It is a Chrome app in which you can create and store requests for later use. It’s handy when you first want to try a web service or test your own APIs. Quick install it here and launch it.

Inside Insomnia, click the upper left purple button and “Create Request.”

Add it to a new group and name it as you want. Now let’s get stuff to work.

Change the request method from GET to POST and add the following URI: https://www3.bcb.gov.br/wssgs/services/FachadaWSSGS?method=getValor

You’ll need to send some parameters in XML format. Just copy…

… and paste it into the BODY field of Insomnia.

You can now make your first attempt at getting data from BCB. You’ll notice though an “error 500” message in the upper right corner. The response says there is “no SOAPAction header!” — let’s fill this info and try again.

Let’s fix it! Go to the HEADER tab and add “soapAction” with value: https://www3.bcb.gov.br/wssgs/services/FachadaWSSGS/getValor

Run the request by clicking on the purple arrow next to the URL.

If everything goes fine, you’ll get your answer. Ours is 0.052461, meaning that’s the value for series “12” — which stands for CDI — on Aug 31st, 2015.

Need further help?❤ Hire me at codementor.io/fwuensche

3. Create a function to use in Excel

In Excel, you’ll need to simulate the same as we did above. To make it simple, we’ll create a function we can call anywhere in our spreadsheet.

(a) Open a new Excel spreadsheet

(b) Press <alt> + F11 to open macros

(c) Right-click on VBAProject and Insert > Module

(d) Paste the code below:

(e) Under Tools > References, add the library: Microsoft XML, v6.0

(f) Close the Visual Basic window

(g) Type the function in any cell: =getValor(12, “31/08/2015”)
Remark: do not forget the double-quotes around the date.

You should get the same value as step 2: 0.052461

Great! Now you can get the values to a specific date by simply:

Note that CDI will only present values for workdays. Avoid errors by surrounding your formula with an IFERROR(value, value_if_error).

So what we’ve got here?

Well, you now have the power to consult the whole Brazilian Central Bank database without ever leaving Excel.

And more, you can find many other useful series through the Time Series Management System. Try searching for IPCA, and you’ll get:

So you can replace the first parameter of “=getValor(seriesId, refDate)” and get a new set of results.

Isn’t it great? Yeah, but…

If you keep a lot of cells requesting data from the web service, your spreadsheet won’t last. It’s going to crash, take far to long to recalculate. So I’d advise you to whether use it on Manual Calculation mode, or to Copy & Paste values after retrieving the data.

Soon enough we’re going to publish a new story on how to get the whole series in a single request. Be the first to be notified!

Need further help?❤ Hire me at codementor.io/fwuensche

Further info

The services definitions (wsdl) can be found at the following URL:
https://www3.bcb.gov.br/sgspub/JSP/sgsgeral/FachadaWSSGS.wsdl

Other available methods

In this tutorial, we covered the getValor method, but there are many others.

getUltimoValorVO:
Get the last value of a series and returns a WSSerieVO object.
Parameters:
- long codigoSerie (code of the series)
Return:
- WSSerieVO (virtual object series)

getUltimoValorXML:
Get the last value of a series and returns the result in XML format.
Parameters:
- long codigoSerie (code of the series)
Return:
- String (string containing the result of the search in XML format)

getValor:
Get the value of series in a given date (dd/MM/yyyy).
Parameters:
- long codigoSerie (code of the series)
- String data (the target date in format “dd/MM/yyyy”)
Return:
- BigDecimal (object containing the value)

getValorEspecial:
Get the value of a special series in a given period.
Parameters:
- long codigoSerie (code of the series)
- String data (the initial date in format “dd/MM/yyyy”)
- String dataFim (the end date in format “dd/MM/yyyy”)
Return:
- BigDecimal (object containing the value)

getValoresSeriesXML:
Get the values of one or more series inside a given period. The result of the search is returned to the client in XML format.
Parameters:
- long[] codigosSeries (list/array of series codes)
- String dataInicio (the initial date in format “dd/MM/yyyy”)
- String dataFim (the end date in format “dd/MM/yyyy”)
Return:
- String (the result of the search in XML format)

getValoresSeriesVO:
Get the values of one or more series inside a given period and returns an array of WSSerieVO object.
Parameters:
- long[] codigosSeries (list/array of series codes)
- String dataInicio (the initial date in format “dd/MM/yyyy”)
- String dataFim (the end date in format “dd/MM/yyyy”)
Return:
- WSSerieVO (list/array of virtual objects)

Support

To get technical information about the services, the Brazilian Central Bank provides support through the email: dine5.deinf@bcb.gov.br

Are you into productivity hot tips for web development? I’m a Brazilian full-stack developer, living and working in Paris, and I regularly share articles like the one you’ve just read. Follow me on twitter to stay tuned.

Brazilian full-stack developer. Living and working in Paris. Sharing productivity hot tips for web development. https://twitter.com/whosantelo

Get the Medium app