The Macrobond Excel formula functions

Overview

Macrobond Excel formula functions give you flexibility and enhance your work in Excel. There are three main use cases:

  1. Importing values or metadata from a particular series to Excel. Values should be put between quotation marks. For example, the following expression will retrieve the last value of the "uscpi" series: 
    =MbndGet("uscpi")
  2. Parameters can be set to reference other cells, meaning that you can generate dynamic queries. Reference cells should be input without quotation marks.
  3. These formulas can be integrated into Excel formulas.

To update your calculations with the latest values from the database:

  • Use the Refresh button on the Macrobond tab
  • Do a deep refresh of the workbook by pressing Ctrl+Alt+F9.

List of formula functions

=MbndGet("series")

Returns the last value of a series.

=MbndGet("series"; "date")

Returns the value of the series at the specified date or, if the date is not associated with a value, a #N/A symbol.

=MbndGet("series"; "startDate"; "endDate"; includeDateColumn)

Returns an Excel array of the observations in the specified date range. If includeDateColumn is TRUE, there will be two columns, with the first column containing the observation dates.

Notes about arrays:

To create an array, you need to have highlighted the cells first. If you consider including the date column, remember to highlight cells for two columns, one for the dates, the other for the values.

Remember to press Ctrl+Shift+Enter when you have entered the formula.

For more information regarding the use of Array functions, refer to Array functions in the documentation provided by Microsoft, within Excel.

=MbndDate("series")

Returns the observation date associated with the last observation. Observation date is the date representing the period which is the first date in the period (i.e., for date 4th May 2004 in Annual series the displayed date will be 1st Jan 2004).

=MbndDate("series"; "date")

Returns the observation date of the most recent observation prior to a certain point in time specified by the second parameter.

=MbndDate("series"; "startDate"; "endDate")

Returns an Excel array of observation dates attributed to the series in the time interval.

=MbndMeta("series"; "name")

Returns the metadata specified by the parameter "name". Metadata names are case sensitive.

Using formulas through 'insert' in Excel

Those formulas work as any other function in Excel.

  1. Go to Formulas tab > Insert functions.
  2. From the list select 'Mbnd.Excel.Udf.Functions'.
  3. Press 'OK'.
  4. Under 'Identifier' type in time series' code.
  5. Press 'OK'.

Question

Can I access vintage data through MbndGet?

Unfortunately, no.

Where do I find the list of metadata available with the MbndMeta Excel function?

The Macrobond Excel formula "MbndMeta(series; name)" returns the metadata identified by the parameter "name" for a particular "series". Metadata names are case sensitive.

In order to find a particular metadata name, you can use the dynamic properties available in Macrobond:

Let’s use the "usgdp" series as an example.

  1. In the Macrobond application, create a new document and add the series "usgdp" to the Series list
  2. In the Time chart, double-click on the title to access the text editor
  3. On the right side of the window, you will see the Dynamic Properties tab, click on it
    • The dynamic properties are based on the metadata which is what we may import into Excel using the function
  4. Scroll through the properties. When you find the metadata, you are looking for, click on the red 'plus' sign


On the picture above, you can see the text {s .Release.NextReleaseEventTime}. The last part of this expression, after the first period, specifies the "name" of the metadata. In this case 'Release.NextReleaseEventTime' should be used as "name" when using MbndMeta.

Use the name of the metadata in a MbndMeta expression as such:
=MbndMeta("usgdp";"Release.NextReleaseEventTime")

We have full list of metadata available under Commonly used metadata.

Why it is not working?

To use UDF function the add-in must be active. See here on how enable it.

Tags: ExcelExcel formulaudf