Tag: 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.

Excel in-house – creating & managing

Purpose and warning

If you want to work with data that is not available in Macrobond, there are multiple options for doing so: Account in–house, SQL and WebAPI Series providers and Excel in-house. If you prefer to store in-house data on your company computers or servers but still want to use the data in Macrobond SQL Connector and WebAPI Series provider are recommended solutions.

Excel in-house also allows to work with data that is stored and processed only within your company.

We discourage use of Excel in-house. This in-house mechanism depends on functionality exposed by MS Excel which is not reliable. It's especially problematic when Excel documents are stored on network shares and accessed by multiple users simultaneously. We've also seen cases of client data loss where information stored only on a local PC of the user was not covered with backup.

 

How it works?

Excel in-house creates a link between the application and an Excel file. This data, and any Macrobond documents made with it, can still be accessed by, and shared between you and your colleagues, if anyone who wants to use it has access to the Excel file the data is stored in.

Creating Excel in-house

You can either upload series directly from within the application, or by creating a template in Excel. All Excel in-house series starts by default from ih:xl but xl can be changed.

To start uploading a series open the My series tab which you’ll find in a list of additional activities. Select Excel in-house from the drop-down menu, and then click on the 'New series' button.

Next, you’ll need to fill in the basic, required information for the series:

  • Series name - unique code used to find series in the application
  • Description - title of the time series
  • Frequency, Start date, Region and Category
  • Optional information - if you’d like to classify the series further

You will need to add direct link to the Excel file where the data is stored. For information how to do this see paragraph below.

How to link Excel file?

Once you’ve filled in the necessary information, you can create the link to your data set in Excel. Open the Excel file with your data in it and make sure to save the file if you haven't done so yet. Copy the columns you want to add data from. Then, go back to the application and click Paste Excel link.

Because we included the dates column in our selection, Macrobond will automatically add frequency and start date information from the file. Otherwise, you’ll need to do so manually. Finally, you can click on OK to complete the task.

Range vs Entire column

Let’s say you intend to add more values to this series at a later point in time. You might have noticed that in the example above we selected whole columns, rather than just those cells that were populated with data. If you selected the entire column when creating the link, Macrobond automatically includes these new values the next time you access this series in the application.

So, for example, if we add two new dates to our file, the application will reflect these changes. If the time series is already open in the application when you update the data set in Excel, simply click on the F5 button, to refresh the series with its latest values.

Adding Excel in-house to a chart

To access in-house series, go to Analytics or Browse tab and select the Excel in-house database from the drop-down menu. Remember to refresh the data tree to show the series you’ve just added.

To add it to a chart, double-click on it or use red ' + ' sign.

Sharing Excel in-house

Sharing Excel in-house requires some preparation from you and your colleagues.

Location of files

First, please make sure that the Excel spreadsheets are in the same location for your colleagues. If you have them in, say, C:/Excel files/, then the same files must be located on your colleague’s computer under C:/Excel files/.

If the spreadsheets are stored on a network drive, then there’s no need to copy them to a local drive, but make sure your colleague has access to the network drive.

Settings for sharing

After ensuring the above conditions are met, you need to create Configuration file path. To do this go to Configuration > Settings > My series (for MB pre-1.28: Edit > Settings > My series), select Add > Excel. Choose the name, prefix (xl in ih:xl) and Configuration file path. Your colleagues can then copy them to their own machine and access in-house Excel series.

Configuration file

What is the Configuration file?

Under Configuration file path you should have a special xml file with the path to Excel file inside it. The xml file shouldn't be replaced with anything else.

How to create the Configuration file?

Go to Configuration > Settings > My series (for MB pre-1.28: Edit > Settings > My series) and select Add >Excel. You will see 'Connector type: Excel' with ready new xml file - it is created automatically.

How to point to the Configuration file?

Press on '...' icon next to configuration file path to make the application point towards the right XML file. For example, it might be under:
C:\Users\user.name\AppData\Local\Macrobond Financial\Excel Provider

How to change path inside XML file?

Open xml file (in Notepad) and change row:

<xl:Path>\\mgi-file1\userdata$\HomeFolder\adunning\Desktop\mb_test.xlsx</xl:Path>

to:

<xl:Path>your network drive path name</xl:Path>

After this save xml file and share it with your colleagues or save it in a shared drive and provide path/link to your colleagues.

Embedding Excel in-house

Excel in-house can be embedded in the document but nobody else will see it.

This is unfortunately how things works. If metadata for data source isn't defined, main-app checks what values are embedded and shows them. In the case of Excel in-house most probably data source is defined in metadata, so the embedded data will not be considered.

Editing Excel in-house

How it works and what is important?

Because Excel in-house series are not stored in the application, managing them differs from the way you modify Account in-house series. The series you access via the Excel in-house solution are simply being mirrored from a particular file in a particular location. Note that:

  • Any changes to a series should be made in the Excel file, not in the application.
  • If you move or rename the Excel file containing your data, the link with Macrobond will be broken. So, remember to update this information in the application.

How to edit Excel in-house series?

Any changes you make to your data set in the linked Excel file will automatically be reflected in the application.

To edit the settings of a series, go to the My series tab, select Excel in-house and navigate to the series.
You can also find it from Analytics or Browse tab. In such case you need to navigate to series and in Actions ribbon select My series > Go to in-house series.
After that select one of the two options that are presented:

  1. Open in Excel – this will open the Excel file directly from Macrobond. Use it when you want to edit data.
  2. Edit link – allows you to access series settings:
  • Modify the reference information about a series, e.g. Description or Category.
  • Update the location or name of the file under path.
  • Edit the data range by changing which cells are being referenced for data.


Click OK to complete the task.

Deleting Excel in-house

To delete an Excel in-house series, go to the My series tab, select Excel in-house and navigate to the series or you can find it from Analytics or Browse tab. In such case you need to navigate to series and in Actions ribbon select My series > Delete in-house series.

A warning will pop up, confirm it to proceed.

VBA codes

Note you have to have installed our Office add-in to use these codes. Add-in installs itself along with Macrobond main-app installation. See below prepared VBA codes to use in Office programs:

Excel

update all data from VBA code

 public sub UpdateMB()
 Dim mbAddIn As COMAddIn
 On Error Resume Next
 
 Set mbAddIn = Application.COMAddIns("Mbnd.Excel.AddIn")
   
 If mbAddIn Is Nothing Then
   'Some error handling
 Else
   On Error GoTo 0
   mbAddIn.Object.RefreshMacroBondData
 End If
 End sub

upload all series from VBA code automatically when opening an Excel file

If you want it to be called automatically, we recommend calling it via a ThisWorkbook /Workbook_Open. 
In certain situations, instantaneous calling (without a few seconds of delay, introduced in Step 1) will cause the macro to fail. 

Step 1: Please add this under ThisWorkbook:
Private Sub Workbook_Open()
   alertTime = Now + TimeValue("00:00:05")
   Application.OnTime alertTime, "UploadSeries"
 End Sub

Step 2: Please add this under Module1

Private Sub UploadSeries()
   Dim mbAddIn As COMAddIn
   On Error Resume Next
 Set mbAddIn = Application.COMAddIns("Mbnd.Excel.AddIn")

 If mbAddIn Is Nothing Then
   'Some error handling
 Else
   On Error GoTo 0
   mbAddIn.Object.UploadMacroBondInhouseData
 End If
 End sub

update a selected chart or object in Excel

Sub Macro1()
    Selection.Verb Verb:=xlOpen
End Sub

update all data from VBA code automatically when opening an Excel file

this can be executed in regular intervals with the Task Scheduler. Typical use case: client wants all excel files automatically opened at 8 AM each day and refreshed with new data.

Step 1: Please add this under ThisWorkbook:

Private Sub Workbook_Open()
   alertTime = Now + TimeValue("00:00:05")
   Application.OnTime alertTime, "MyRefresh"
End Sub

Step 2: Please add this under Module1:

Private Sub MyRefresh()
   Dim mbAddIn As COMAddIn
   On Error Resume Next
       Set mbAddIn = Application.COMAddIns("Mbnd.Excel.AddIn")
   If mbAddIn Is Nothing Then
       'Some error handling
   Else
       On Error GoTo 0
       mbAddIn.Object.RefreshMacroBondData
   End If
   ThisWorkbook.Save
End Sub

upload in-house templates [all sheets - or specific range] from VBA code

public sub UploadMBInhouseData()
    Dim mbAddIn As COMAddIn
    On Error Resume Next
 
    Set mbAddIn = Application.COMAddIns("Mbnd.Excel.AddIn")
   
    If mbAddIn Is Nothing Then
      'Some error handling
    Else
      On Error GoTo 0
  
     ' You can either specify a static range like this:
     ' Set UpdateRange = Range("C1", "F1")
     ' Call mbAddIn.Object.UploadMacroBondInhouseData(UpdateRange)
  
     ' Or use the current cell selection to specify the range like this:
     ' Set UpdateRange = Application.Selection
     ' Call mbAddIn.Object.UploadMacroBondInhouseData(UpdateRange)
 
     ' This will update all in-house series.
     Call mbAddIn.Object.UploadMacroBondInhouseData()
    End If
 End sub

refresh current worksheet only

Sub a()
 For Each shp In ActiveWorkbook.ActiveSheet.Shapes
   If shp.Type = msoEmbeddedOLEObject Then
       If Not shp.oleformat Is Nothing Then
           Dim oleformat
           Set oleformat = shp.oleformat
           If oleformat.progID = "Mbnd.EmbeddedDataStore" Then

             On Error GoTo ErrorHandler:
               shp.Select
               Selection.Verb Verb:=xlOpen
             End If
             ErrorHandler:

           End If
       End If
 Next
End Sub

refresh charts in the current sheet

Sub RefreshMacrobondCharts()
    Dim sh As Shape
    Dim I As Integer
    For Each sh In ActiveSheet.Shapes
        sh.Select
        Selection.Verb Verb:=xlOpen
    Next
End Sub

Word

refresh charts

Sub RefreshMacrobondCharts()
    For I = 1 To ActiveDocument.InlineShapes.Count
        If Not ActiveDocument.InlineShapes.Item(I).OLEFormat Is Nothing Then
            ActiveDocument.InlineShapes.Item(I).OLEFormat.DoVerb VerbIndex:=1
        End If
    Next
End Sub

resize charts

Sub test()
Dim sRep
sRep = ""
For i = 1 To ActiveDocument.InlineShapes.Count
 Dim shape
 Set shape = ActiveDocument.InlineShapes(i)
 If shape.Type = msoEmbeddedOLEObject And Not shape.oleformat Is Nothing Then
  Dim oleformat
  Set oleformat = shape.oleformat
  If oleformat.ProgID = "Mbnd.mbnd" Then
  shape.Width = 150
  shape.Height = 150
 End If
End If
Next
End Sub

Powerpoint

refreshcharts

Sub RefreshPPT()
For SlideID = 1 To ActivePresentation.Slides.Count
Dim slide
Set slide = ActivePresentation.Slides(SlideID)
For shapeId = 1 To slide.Shapes.Count
Set shape = ActivePresentation.Slides(SlideID).Shapes(shapeId)
ActiveWindow.ViewType = ppViewSlide
If shape.Type = msoEmbeddedOLEObject Then
ActiveWindow.Selection.Unselect
ActiveWindow.View.GotoSlide slide.SlideIndex
shape.Select
  If Not shape.oleformat Is Nothing Then
   Dim oleformat
   Set oleformat = shape.oleformat
    If oleformat.ProgID = "Mbnd.mbnd" Then
     shape.oleformat.DoVerb (2)
    End If
   End If
  End If
Next
Next
End Sub

resize charts

For SlideID = 1 To ActivePresentation.Slides.Count
 Dim slide
 Set slide = ActivePresentation.Slides(SlideID)
 For shapeId = 1 To slide.Shapes.Count
  
  Set Shape = ActivePresentation.Slides(SlideID).Shapes(shapeId)
  If Shape.Type = msoEmbeddedOLEObject Then
  If Not Shape.oleformat Is Nothing Then
   Dim oleformat
   Set oleformat = Shape.oleformat
   If oleformat.ProgID = "Mbnd.mbnd" Then
       Shape.Width = 50
       Shape.Height = 50
       
   End If
   End If
  End If
 Next
Next

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.

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")
or depending on your Excel's settings:
=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)
or depending on your Excel's settings:
=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")
or depending on your Excel's settings:
=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")
or depending on your Excel's settings:
=MbndDate("series", "startDate", "endDate")

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

=MbndMeta("series"; "name")
or depending on your Excel's settings:
=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)" (or "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") (or =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.

What’s in Macrobond settings in Excel?

The settings dialog box in the Excel add-in allows you to adjust your Macrobond account credentials, communication settings, and document directories directly from the Excel add-in.

Account

In the account tab, your Macrobond username and password, as provided by your sales representative, are stored. These credentials enable access to the database packages included in your license.

Communication

In this tab, you can define settings for a proxy server if it is not detected automatically. Additionally, it’s possible to specify the URL of the Macrobond server. In most cases, however, the default option should remain selected.

The "Automated configuration" button lets you test a number of communication configurations to see what works best for your system.

Selecting "Verify end-to-end encryption for https" verifies that the communication is encrypted between the application and the Macrobond servers and detects if anyone is eavesdropping on the communication. The option might not work in some networks where a firewall is configured to intercept and inspect all internet traffic.

Advanced

The "Enable application logging" option logs more detailed diagnostically relevant information. Because turning on this function may impact application performance negatively, it should only be turned on when prompted by Macrobond support.

Checking "Enable SQL and Web API Series provider database connector log console" activates a popup window providing details about the communication with your SQL server. This option should be used when creating or troubleshooting the SQL in-house data storage.

When the "Collect usage statistics" option is checked, the application will gather anonymous information about the application’s performance and your feature usage. These statistics help us to constantly improve your experience.

Document paths

In this tab, you can specify a location on your computer where you’d like to save the Macrobond documents that you choose to save locally rather than on the Macrobond server. By default, the Macrobond folder is located in your Documents folder.

Furthermore, you can add and name frequently used directories for easy access when opening and saving documents.