Category: MB in Excel

Accessing Macrobond data in Excel

There are two ways to access Macrobond data in Excel. You can either start in Excel, perform a direct download using the Macrobond add-in. Or you can start in the application and download the calculations you’ve performed there to Excel. Both ways will be described below.

Direct download from Excel add-in

If you’re working in Excel and would like to download data directly, you’ll use the Overview or All series buttons under the Macrobond tab.

You can either select from a list of key indicators or from all the data in the application. As a quick example let’s look into Flow of Funds data for the US. This gives you the option of going through all the different categories to select what you’d like to download. Once downloaded the data will be organized in the same way as it is presented when selecting it. You can also view series metadata.

Copy as Excel data set from Macrobond

Let’s say you’d like to apply more advanced calculations to the data, then 'Copy as Excel Data set' is a good method to use. It is typically much simpler to apply certain analyses in the application than it would be to use an Excel formula to do so. To demonstrate, let’s look into the National Accounts data for India. These time series are not seasonally adjusted so, after finding the data, you’d perform the analysis in Macrobond and then simply copy the analyzed data by right clicking on analysis and choosing 'Copy as Excel Data set'. After this you can paste it into Excel by pressing Ctrl+V.

This will create red block in Excel, which is called the Data set object. It contains all the information about the downloaded data set. It also acts as a reference for where the data is located in the sheet. So, don’t delete it. If you’d like to move or make changes to the data set object have a look at Working with All series mode article.

Updating data sets in Excel

If you want to update the downloaded data sets to reflect the latest data release, click on Refresh. Charts are also automatically updated using the same mechanism.

Working with All series mode

Positioning the data set

When you work in Excel, organizing data is crucial, especially if you download a lot of data sets from Macrobond into one sheet. Each data set you download from the Macrobond add-in comes with a red box like the one you see below. We call this the data set object, and it stores your downloaded data set as well as its settings.

By default, the location of the data is linked to the position of the red data set object. This allows you to move the entire data set by dragging the object to a new position. It’s a convenient way to organize your Excel file before working with the data.

So, when you start creating links and calculations with the data, it may be best to have the data set locked in a specific cell so that these links don’t get broken or reference the wrong cells.

To lock the data set, you need to access the Edit window. Right click on the data set object and select Edit. Here, you’ll find the Automatic positioning setting. Just uncheck the box, and enter the cell you want the data set to start in. Now the data set is locked to a specific cell, meaning that if we move the red object, the data will remain at the location we specified. This way, you don’t have to worry about updating links that you create in Excel.

Adding metadata

Metadata refers to additional information about a time series, like, for example, its source or next release date. When you download time series into Excel, you may want to include some metadata so that you can easily view this type of information without leaving Excel.

You can add metadata by right-clicking on the Macrobond data set object and clicking Edit. Click on Additional fields drop-down to select what you’d like to add from a list of the most commonly used options.

You can also access other metadata options, here. In the new window that opens, the options are organized by type.

It is also possible to set a default list of metadata that will be included every time you download data to Excel. All you have to do is select the options you want and then click on Save as default.


As you may notice the all metadata is added above the time series so that it’s easy to check.

Inserting columns & rows

You might be wondering why you can’t just add your data and calculations the ordinary way in Excel. If you did add them that way, the output wouldn’t be read by the application as part of the data set and wouldn’t be maintained.

To start, you need to add empty cells where your data will be placed. Open the Edit data set window and find Orientation in the Output column. Choose Column or Row based on the orientation of your data set. To finish off, click on Add blank item button. By default, the blank column will be added at the end of the data set, If you want to place it elsewhere, simply drag it to the appropriate position.
If you need to add a lot of blank cells, or have a large data set, you might want to avoid manually dragging the columns or rows into place. To set the location before inserting them, navigate to the series basket. Select a series, right click on it and choose to insert the cells above or below this point.
Any text, values or calculations added to the cells within these columns or rows will be maintained when you refresh the data set.

Locating series in the data tree

Locating series in the Macrobond data tree is useful when you want to find data related to a particular series you’re working with.

We’ll look at two examples. In the first we locate data we’ve already downloaded in the data tree to select more series. Then we go on to show you how to switch between views of the database.

Selecting more series

Right-click on one of the time series, select Locate in tree and choose the same database view we used to download the data. Let’s say that was Source & Release. Now we can browse related data from the same source.

Switching between database views

This feature can also be used to find the same series in another location in the database. Instead of having to look through the data tree from scratch for the other series, you can use the Locate in tree feature. The Concept & Category view of the database is ideal for cross-country comparisons, so right click, choose Locate in tree and select Concept & Category. Now you can simply scroll through the list to select the same series for other countries.

Import data from a saved chart

By pressing Import button you can download data used in your saved charts directly to Excel.

Double-click on a chart and it will open as 'red object' panel. Press OK and it will be added on the sheet.

Overview mode

With Overview tool you can download overview data for selected entity.
This same feature as in Macrobond main-app under Browse tab > Overview.

1. Pick a country or company

Here, you select the country or company that you want to include in your report.

2. Type of overview

Here, you can select type of data.

3. Settings

In this area you can control a few things about the data in the object which will be created.

4. Entities

The tabs here are separate time series that will be downloaded into one object.

Create reports in Excel

With the report tool in Excel, you can quickly get an overview of countries or companies by downloading Macrobond’s selected key indicators for them.
This same feature as in Macrobond main app under Browse tab > Overview > Reports.

1. Pick a country or company

Here, you select the country or company that you want to include in your report.

2. Add to a report

This area shows the actions that can be taken with the selected entity. You can add it to an active report if it exists, or start a separate report with the available data, such as key indicators.

3. Reports

The tabs here are separate reports that will be downloaded into separate Excel worksheets. Each tab lists the countries or companies included.

Processed/Raw data selection

When downloading data from the application database to Excel, you will need to select one of two download modes: Processed and Raw. The primary difference between the two options whether start dates and/or frequencies have been harmonized. Which one of the options you select will effect which settings that are available to you from the download panel.

Which mode to use

  • Processed mode offers more settings for adjusting data before you download it and allows you to harmonize the data set in terms of frequency and start date. You can also apply calculations through that mode.
  • Raw mode is useful when you want to download multiple series while keeping their original form in terms of frequency, scale, etc.

Processed

  • Time series are presented relative to a single date column.
  • Data can be harmonized across parameters such as date range and frequency.
  • Choose from pre-defined calculations such as Rate of change and scale to apply to the data.

Adding calculations in the Processed mode

When using Processed mode to download data to Excel, there are a few basic calculations which are possible to use.

To perform calculations on your data set, you need to open the same window you used to download the data. To edit the data set, right-click there and select Edit. In the list of series, you’ll see that the analysis column contains an Edit button, which you can click to apply calculations.

As mentioned there's few calculations which you can use without opening document in Macrobond:

Rate of change

Similarly as in Macrobond Rate of change analysis, you can choose Method of calculation and Length, however it is not possible to use Mode option here.

Scale

It is also possible to adjust series scale in Processed mode. To do this you need to select the relevant option from the scale drop-down.


In both cases to apply the same settings to all the series in the list, use the Fill-down button. Click Update when you are ready to apply your changes to the data set.

When you use Refresh to update your document with the latest values, all your calculations will also automatically be updated.

Raw

  • Download time series in their original state and frequency.
  • No predefined calculations can be applied.
  • Each time series is presented with a data column.
  • Each series can have its own start date.


All Series mode

All series mode is the main access point for downloading data into Excel. It gives you access to your entire application database and provides various options for downloading the data in a specific format.

The All series workspace

  1. Series browser
  2. Download settings
  3. Data download list

Purpose

Use it to:

  • Search through all the time series available from the application database.
  • Select the data you want to work with.
  • Apply settings that modify the series before downloading the data to an Excel document.
  • Verify the selected time series and metadata in the download list, before adding to the excel sheet.

Have a look at the follow up materials to find out how to use the settings you’ll encounter when downloading data in All series mode.

Using Macrobond data in Excel

Far be it from us to tell you where you work best. At times, what works best for you might be Excel, even if you still want the benefits of using the Macrobond database. Working with Macrobond data in Excel can be very efficient, and probably more flexible than you’d expect.