6. 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.
Accessing Macrobond data in Excel - overview video
[01 Excel Add-in - Introduction]
This video will introduce you to downloading Macrobond data for use in Excel. You can either start in Excel and 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. We’ll also have a quick look at how to update the data you’ve downloaded.
[02 Add-in grouping]
To start, click on the Macrobond tab to access the tools. For now, we’ll focus on these tools, here. The remaining tools are used for editing datasets, as well as uploading your own data to be used in Macrobond. We’ll cover these in a later tutorial.
[03 Downloading - Direct]
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.
[04 Downloading – Metadata]
You can also view series metadata. You’ll see here that there are options for making changes to the data before downloading, but we’ll look at those options in a later tutorial.
[05 Downloading – Macrobond document]
Let’s say you’d like to apply more advanced calculations to the data, then this 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, and paste it into Excel.
[06 Downloading – Dataset object]
This red block is called the Dataset object, it contains all the information about the downloaded dataset. 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 dataset object have a look at the follow up material on this topic in the Curriculum.
[07 Downloading – Refresh]
If you want to update the downloaded datasets to reflect the latest data release, click on Refresh. Charts are also automatically updated using the same mechanism.
[08 What’s next?]
In this video, we covered the two main options for downloading data and refreshing it. To learn more about working in Excel have a look at some of the follow up material covering topics like making changes to Macrobond time series and data sets, performing dynamic queries, using Excel to upload your own time-series to Macrobond, as well as the next steps for working with data in Excel.
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
- Series browser
- Download settings
- Data download list
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.
When downloading data from the application database to Excel, you will need to select one of two download modes: processed or raw. The primary difference between the two options whether or not 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 dataset 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.
- 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
- 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
When you use Processed mode to download data to Excel, there are a few basic calculations you can apply. In this video, we’ll take a look at changing the scale of the values in your dataset, applying a Rate of Change calculation, as well as what happens to calculations after you update the data using the Refresh function.
[02 Modifying Scale]
To perform calculations on your dataset, you need to open the same window you used to download the data. To edit the data set, right-click here 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. We’ll start off by adjusting the scale to billions. Select the relevant option from the scale drop down. 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 dataset.
[03 Adding Rate of Change analysis]
Next let’s look at applying a Rate of Change calculation. The process is the same as before. Click on the Rate of Change drop down and select the appropriate option - you can either calculate a percentage or a value. 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 dataset.
[04 Calculations after update]
When you use Refresh to update your document with the latest values, all your calculations will also automatically be updated.