The Macrobond Excel add-in
This document refers to Macrobond 1.13 and later.
With the Macrobond Excel add-in you can import data from Macrobond into Microsoft Excel and easily keep it updated with the latest values. You can also upload data from Excel and use as in-house series in Macrobond.
Depending on your version of Excel, the add-in can look a bit different.
You can add data in two forms:
- As a Time series - this will import all the values of one or more time series that you select.
- As a Report - this will import the last value of predefined reports, such as company account data.
Import time series
There are three different methods that can be used for importing time series from Macrobond into Excel: selecting series by using the data navigator, use series from country and company overviews or use the result of a calculation in a Macrobond document.
Select series by using the data navigator
The All series button in the Macrobond add-in toolbar/menu will bring up a dialog like this:
You then select one or more time series using the database navigator.
There are two import modes:
|Processed||All series in the set are converted to the same frequency and calendar and there is only one column/row of dates. You may select rate of change calculations and scale for each series.|
|Raw||Series are treated separately and there is a column/row of dates for each series.|
Get series from country and company overviews
The Overview button in the toolbar/menu allows you to do cross-country or cross-company comparisons of data in Excel. Selecting it will display a dialog like this:
Add one or more countries or companies by using the Database navigator. Then select the type of overview.
Use the result from a Macrobond document
You can use the result of any calculation in a Macrobond document and turn it into a Macrobond Excel Data set. There are several ways you can do this:
- You can press the Import button/menu item in the Macrobond Excel add-in and select a saved Macrobond document.
- You can select “Copy as Excel Data set” on the Edit or context menu when an analysis is selected in a Macrobond document that you can paste into Excel:
- Select values in a Table presentation in the Macrobond application and then select “Copy special” from the Edit or context menu. Select to copy a Data set instead of Text and then paste the result into Excel:
When you copy or import time series from a Macrobond document into a Data set, a copy of all calculations is stored in the Data set and there is no dependency on the document where the data came from. In order to edit the calculations used by the Data set, press the “Edit document…” button:
This will open a document tab in the Macrobond application where you can view and change the calculation settings.
When you have imported data to the Excel sheet you will notice a red object in your sheet with the text “Macrobond Data set”. It is this object that keeps track of what series and settings you have selected.
If you select the red Data set objet and press the “Edit selected data set” in the toolbar/menu, you will once again see the dialog where you select series and settings.
When you press the Refresh button in the toolbar/menu, all data in the current workbook will be updated with the latest information.
Data is always placed in the sheet relative the Data set object. If you move the Data set object, you will notice that the data is imported to a new position the next time you select Refresh.
Please note that if you remove the Data set object, the information can no longer be updated when you select Refresh.
You can have any number of Data sets in a workbook.
Press the Report button in the toolbar/menu to bring up the report selection dialog. When you select an entity, such as a company or a country, for which there are reports, you will see a list of the reports at the top of the dialog. When you select a report then a new tab will be created on the right hand side.
You can add more entities to each report by selecting the entity and then pressing “Add to active report”.
Each new report will be imported to a new sheet.
Uploading in-house series
You can upload time series data from an Excel sheet into your in-house account database in Macrobond. This is done by creating a template in your Excel sheet that contains information about each time series. The template must follow a strict layout. The easiest way to create the template is to press the “Create template” button/menu item in the Macrobond Excel add-in (please note, that you need to select a record in the first row, in your Excel spreadsheet, prior to pressing this button). This will bring up a dialog where you fill in the information needed to create the template:
Additionally, in the Optional information tab, you can mark values of the in-house series as forecast, before uploading them to Macrobond. In order to do that, tick off the option Add a column that indicates if a value is a forecast. Please note that adding a forecast column is possible only while creating a template. In case you are modifying already existing template, this option is not active.
When you press Create, the template is created in you sheet:
In order to mark a the series values as forecasts you should use =TRUE formula in the cells.
You can edit the template by selecting the “Edit template” button/menu item in the add-in.
You need to fill in at least one date and one or more values. If only the first date is specified, the selected frequency will used to deduct the following dates.
You can have several templates in the same sheet. If you want to use the same dates for another series, you can copy the column of values like this:
You use the “Upload all templates” button/menu item, which will look for templates on all the sheets in the workbook, or “Upload selected templates”, which will look for templates in the range of selected cells.
If you upload new series while the Macrobond application is running, you might have to press the Refresh button in order to see the new series immediately:\
The Macrobond Excel formula functions
If you want to retrieve just a few values from a time series, you can use the Macrobond Excel formula functions to retrieve values and meta data. For example, this expression will retrieve the latest value of the uscpi series:
The formula functions have the benefit that the parameters can reference other cells and that the values can be used directly as a part of a formula.
In order to update the calculations with the latest values from the database, you can use the Refresh button in the Macrobond toolbar/menu or do a “deep refresh” of the workbook by pressing Ctrl+Alt+F9.
Returns the last value of a series
Returns the value at the specified date or, if the date does not exist, the value before that date.
MbndGet(series, startDate, endDate, includeDateColumn)
Returns an Excel array of the observations in the range of dates. If includeDateColumn is “True”, there will be two columns and the first will contain the observation dates. Read more about “array functions” in the Excel documentation and be sure to press Ctrl+Shift+Enter after entering the formula.
The date of the last observation.
The date of the observation specified by the second parameter.
MbndDate(series, startDate, endDate)
Returns an Excel array of dates.
Returns the meta data identified by the parameter “name” for the series. Commonly used meta data are “FullDescription”, “Unit”, “Frequency”, “Currency”, “Region”, “ForecastCutoffDate”, "Release.NextReleaseEventTime", "Release.LastReleaseEventTime". One way to find out what metadata a series has is to use the Dynamic text editor in a chart in the Macrobond application.