Excel in-house – creating & managing

Purpose

If you want to work with data that is not available in Macrobond, there are two options for doing so, Account in–house 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, the Excel in-house solution is a good fit. Note that this method will need some preparations from you.

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.

See here pre-1.27 version view

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

See here pre-1.27 version view

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.

Tags: add-inExcelExcel in-houseexternal dataMy series