Analyzing and transforming data

How do you calculate a Z-Score / Standardize a series?

Use the standardize function:

Standardize(sek)

Standardize(sek, YearsLength(3)) - If you want to use a rolling window of observations, in this example we have used a 3-year window of observations.



Why does the Performance analysis not start with 0?

The Performance analysis calculates the change since the observation before the specified Start date, so the first value cannot be zero. It includes one extra observation for calculation than what is entered – you can bypass this by setting the Start date one observation later.



For more information on Performance analysis: Performance analysis



How do I annualize a monthly P/P series with a formula?

pow((1+(series/100)), YearLength())-1



How do you rebase a series?

Add or insert the Rebase analysis. Set the Method, Point in time and Base value

This is different from the Index builder analysis, which is used for portfolio construction.

Weighted index from Index builder analysis

Rebase in Slice analysis





How do I create an if() condition / statement?

Syntax:

if(condition, value1, value2)

which can be translated as if(condition, if_TRUE_then, if_FALSE_then). For example:

if(sek>8, sek, 0)

For more information, go here: Creating AND/OR if() formula



How do you set a common currency in a document?

Click on the Series list and select your desired currency from the Currency drop-down list



For more information about the Currency Conversion process, please read: Currency conversion



How do I create an if() condition / statement with AND/OR parameter?

If you want to create a condition with “AND” use “&” in the if syntax:

if(condition1&condition2, value1, value2)

which can be translated as if(condition1 AND condition2, if_TRUE_then, if_FALSE_then). For example:

if(sek>7 & sek<8, sek, 0)

If you want to create condition with OR use “|” in formula:

if(condition1|condition2, value1, value2)

which can be translated as if(condition1 OR condition2, if_TRUE_then, if_FALSE_then). For example:

if(sek>7 | sek<9, sek, 0)

For more information, go here: Creating if() formula



How do I aggregate a series?

Use the Aggregate analysis – setting the period of aggregation: weekly to annual or all.

Another way of aggregating a series is by using one of the functions below:

AggregateSum(series) – it corresponds to Aggregate > All using the Aggregate analysis

AggregateSumAnnual(series) - it corresponds to Aggregate > annual using the Aggregate analysis



How to join series with different scales?

If you want to join two series that have different scales, there are two formulas you can use, depending on the purpose:

JoinScaled()or JoinMoreHistory().

The difference between JoinScaled and JoinMoreHistoryScaled lies on the order of the series and the way you want to join them.



With JoinScaled you prolong “series1” with “series2” by scaling “series1” at the date set for the junction.

JoinScaled(series1, series2)

JoinScaled(series1, series2, observation)

These formulas will join two series together and scale “series1”. The difference between the two formulas is the date at which you want to join the series. The first option will join the two series at the end of “series1”, while the second option will join both series at an “observation” set by you.



If you want to join two series that have different scales, but with the purpose of creating a series with more history, you should use:

JoinMoreHistoryScaled(series1, series2)

This formula will return a series containing all observations of “series1”, complemented with the observations of “series2” that are before the start of “series1”. The values of “series2” will be scaled so that they are equal to the beginning of “series1”.

With JoinMoreHistoryScaled, you add more history to “series1” with data from “series2”, by scaling “series2” based on the first observation of “series1”.



How do you calculate the median value for a series?
  1. Add or Insert Statistics analysis,
  2. Click on Add and choose Median as the method

Optionally, you can set the date range for the calculation by filling in From and To.





How do I splice two series together?

You can use the join function to splice two series together.

join(series1, series2, date)

which can be translated as join(older_series, newer_series, date). The last parameter is optional. If not provided, it will use older_series until its last observation, then switch to newer_series.

Examples:

join(imf_q69466___zf, ngprod0001)



To reuse the history of an older series in a new one:

Join(imf_q69466___zf, ngprod0001, Start(ngprod0001))

or

JoinMoreHistory(ngprod0001, imf_q69466___zf)



If you want to join series at a specific date:

Join(imf_q69466___zf, ngprod0001, Date(2007, 1, 1))

For more information on Join(): here.



How to sum several series together?

Use formula language to sum series with “+” sign in Series list.

Example:

sek+nok+dkk



If you have a lot of time series to sum up, an easier way is to use Cross Section analysis by adding “Sum”.



How to do a formula if I want it to return 0 if the cell is blank?

Please use formula:

Null0(series)

For more information on formula language:

Formula language



How do I calculate a spread, or subtract one series from another?

Using Arithmetic analysis:

  1. add an Arithmetic analysis
  2. choose “subtract” from the 1st operation field
  3. select the series you wish to subtract from the series operand field.

Directly in the series Series list:

pt10ygov-de10ygov

Using Formula analysis:

fx:s1-fx:s2



How do you add a moving average / calculate a rolling mean?

Insert or add a Smoothing analysis and select:

Add -> Moving average

You can set the Length parameter in the analysis. This calculation can also be performed with the formula:

>mean(series, window)

For example, for one year rolling mean, please use:

mean(segdp, YearsLength(1))





How do you compare different time periods of a series in the Slice analysis?

From the Periods drop-down list choose the period and set the Calculation range, leave blank if you want it to cover the full history for the time series.

Additionally, you can rebase each sliced period to a specific point in time by checking the Rebase with base value.



For more information on Slice:

Custom ranges

Custom points in time



Why outcomes from Rate of change and Scalar's Rate of change since are different?

Scalar’s calculation ‘Rate of change since’ works differently than Rate of change analysis as you can (and sometimes need) to specify there ‘Range start’ and ‘Range end’. Rate of change analysis calculates values from the end of each series while ‘Rate of change since’ in Scalar from the end of the whole calendar – that is why you can set ‘Range end’.

If one of the series has more observations than others, to select same time period - and get same value -as on Rate of change, please specify in Scalar also ‘Range end’.

Example:

Rate of change analysis

Scalar analysis



How do you calculate percentage change over period (Y/Y, Q/Q, P/P, M/M)?

Add or insert the Rate of change analysis. Set the Method as Change over period %, set the Length parameter.

You can also calculate percentage change over period with a formula. Syntax:

cop(series, length)

Examples for Y/Y, M/M and Q/Q changes, respectively:

cop(segdp, YearsLength(1))

cop(segdp, MonthsLength(1))

cop(segdp, QuartersLength(1))





How do you use custom ranges in the Slice analysis?

Select Custom ranges and then set the Start time and End time range. The range can be closed or open-ended:

The result can be presented as a Category chart, a Category scatter chart or a Category table.



For more information on Slice:

Slice

Custom points in time



How do you use custom points in time in the Slice analysis?

Set the date (e.g., 1929-09-21) at “points in time”, set the number of observations (e.g., 2500), and select the type of each point:



For more information on Slice:

Slice

Custom ranges



How to calculate a rolling correlation between series?

It can be calculated using the Covariation analysis. Choose the explanatory series and set “Length” to specify rolling period.



How to write dates in the Macrobond formula language?

In the Macrobond formula language, dates can only be written using the Date Formula: Date(year, month, day)

For instance: Date(2017, 01, 01)

The formula will return the observation number that refers to that date.



How do I “disaggregate” a series? (Some Chinese data is published by aggregating the periods for each year)

Series which are aggregated annually can be “disaggregated” by using an If() statement:

If(Counter()=StartOfYear(), cngpfi0091, Momentum(cngpfi0091, 1))

The logic of the above if statement is: keep the first value of each year as given but the subsequent values in that year are calculated by subtracting the previous value from the current value.



How do I perform a rolling aggregation of a series?

Use the sum(series, window) function, for example:

sum(usflof8344, 8) – this aggregates usflof8344 on a rolling 8-observation periods.

sum(segdp, YearLength()) - this aggregates segdp using the YearLength() function as input for the window parameter it evaluates to the number of observations in a year.

You can also use Aggregate analysis. The difference between those two methods is that in the Aggregate analysis, you don't have the flexibility of the formula language, meaning that you have less possibilities in terms of setting the length.



How do I lag or lead a series?

Use the Lag analysis you can decide the direction (Method) and the length (Base):

For the base you can specify: observation, year, quarter, month, week and its multiples.



Another way of lagging a series is to use the Lag function: Lag(series, length)

The function returns the series lagged by the length parameter specified. The length parameter is rounded to an integer.

Examples:

Lag(sek, -2) – this will move the series 2 observations backward

Lag(sek, YearLength())– this will move the series 1 year forward



How do you change the frequency in a document?

Click on the Series list and select your desired frequency from the frequency drop-down list.

This operation takes precedence before all other calculations, including formula functions on the Series list. If you wish to change the frequency after having performed some analysis, use the: Frequency conversion analysis.



How do you include discontinued series in my search results?

Choose "Include discontinued" from Filter drop-down list in Search bar:



How do I shorten a series?

One of the three variants of the Cut function can be used:

CutStart(series, observation)

CutEnd(series, observation)

Cut(series, observationStart, observationEnd)



CutStart is an open-ended cut – it removes all observations prior to a specified date. For example:

CutStart (segdp , Date(1970 , 01 , 05))

CutEnd removes all observations after a specified date. For example:

CutEnd(segdp , Date(2010 , 01 , 05))

Cut only leaves the observations that fit within the specified range. For example:

CutStart (segdp , Date(1970 , 01 , 05), Date(2010 , 01 , 05))



How do you add AAA, BBB, etc. scale /ratings in a document (for Merrill Lynch series)?

Add value labels to the chart:

  1. Click in the chart area:
  2. Presentation properties -> Elements
  3. Tick the checkbox ‘‘Vertical axis value labels’’

Click on a label and go to: Presentation properties ->Text -> Edit

Choose 'Merrill Lynch composite rating' from the Dynamic properties

{s .MLRating}

For Merrill Lynch series a conversion table between Rating and Number is available in the Time series information:

Actions -> Presentation -> Time series information

Dynamic Text

Adding x-axis values





How do I calculate / draw Bollinger lines?

Use the BollingerUpper and BollingerLower functions. The syntax is:

BollingerLower(series, number of Std Devs, window)

BollingerUpper(series, number of Std Devs, window)

Examples for sek:

BollingerLower(sek, 2, YearLength())

BollingerUpper(sek, 2, YearLength())



How do I replace all missing values in a time series with a 0?

Use:

Null0(series)



For more information on formula language: The Macrobond formula language



How do I output coefficient / beta in Regression?

Slope(series1, series2)

Slope(series1, series2, window)

The above formulas return the coefficient of the regression, a.k.a. the beta, between series 1 and series 2 where series 1 is the dependent series.



How do I calculate the percentile value of an observation in a series?

Use the the two following functions:

PercentRank(series, value)

PercentRank(series, value, length)

"value" is a value that is compared against the series - for example:

PercentRank(sek, sek)

PercentRank(sek, sek, YearsLength(3))



How does automatic currency conversion work?

The automatic currency conversion will use a currency conversion series (paired to USD) matching the frequency of the document. If no such series is found, it will use an exchange rate series with a higher frequency and take the first observation of the period.



How do you change a series from a ratio to a percentage?

To convert a ratio to a percentage you need to multiply the ratio by 100, this can be done in the following 2 ways:



How do you remove a series from a chart or from a document?

Click on the line in the chart you wish to delete then press the delete key. You can also delete it from the graph layout window which is accessible by right clicking on the chart and choosing graph layout from the context menu or by clicking on the Graph layout button found in the top menu or by using the Ctrl+L short cut key.

If you wish to remove a series from a document completely you will need to delete it from the Series list.



How to mimic the frequency conversion with formula language?

At(series, StartOfYear())

Uses the first observation of the year as a method of conversion and fills all observations from the entire calendar year with it.



At(series, EndOfYear())

Uses the last observation of the year as a method of conversion and fills all observations from the entire calendar year with it.



At(AggregateSumAnnual(fx:s1), EndOfYear())

Aggregates all observations within a calendar year and fills all observations from the entire calendar year with last value of this period.



At(Mean(fx:s1, YearLength()), EndOfYear())

Calculates average of the all observations within a year and fills the entire calendar year with last value of this period.



At(High(fx:s1, YearLength()), EndOfYear())

Calculates the highest value of the all observations within a year and fills the entire calendar year with the last value of this period.



At(Low(fx:s1, YearLength()), EndOfYear())

Calculates the lowest value of the all observations within a year and fills the entire calendar year with the last value of this period.



How do I embed an in-house series in a document?

There are two ways of embedding an in-house series into a document.

Regardless of the method used, the recipient of the file will be able to see the embedded series.



How do I create an index?

You may want to create an index that measures self-financing investment strategy returns of a portfolio or as a weighted average of a number of series. The Series operations -> Index builder analysis should be used in this case.

faq91

There are several calculation methods to choose from - for more details please refer to the document available at: https://help.macrobond.com/analyses/operations/indexbuilder/.

You may also want to scale the series to start at some specific value, like 100. The Transofmations -> Rebase analysis should be used in this case.

faq92

Here, decide which rebase method you would like to apply, together with the Point in time and the Base value.



How do you calculate R2 parameter using a formula?

Pow(Correlation(fx:s2, fx:s1), 2)

where fx:s2 is the dependent.

Regression analysis

Regression line



How do you calculate a regression line using a formula?

You can generate the y=ax+b formula using the functions:

Intercept(series1, series2)

And

Slope(series1, series2)

Regression analysis

Regression line



How do you add a trend line?

Add or insert the Statistics analysis and select:

Add -> Trend

More information:

Trend channel / trend band

Regression line

Regression analysis





How do you add a trend channel / trend band?

Insert a trend line first.

Check the boxes to add +- Std dev.

More information:

Trend line





How do I add a forecast to a time series?

You can add the forecast either to the original time series or after applying some calculations to it.

In the first case - in order to add the forecast to the original time series, before any calculations are made - when working in the Analytics activity and willing to extend a time series already added to the Series list (in the Analysis tree), select there the Forecasts tab.

faq31

In the displayed ‘Edit forecast’ window, click on the ‘Edit’ button next to the time series and add the new values in the ‘Forecast value’ column. These data will be visible in the chart as forecasts. You can also add a value on the last point you would like to forecast and set Linear interpolation for missing values method in Conversion settings tab.

faq32

Another option is to use the following formula in the Series list:
if(Counter() < LastValid(series), FlagForecast(ExtendLinear(series, Date(year, month, day), observation number)), series)
where:
Counter() - returns a series of all observation numbers
LastValid(series) - returns the last value of a series that is not null
FlagForecast(value, condition) - returns a value as a forecast value if the condition is True and as a non-forecast value if it is False
ExtendLinear(series, observation, number) - returns a series extended with a specified number using a linear interpolation from the end until the specified observation number

In the second case - in order to add the forecast to the time series after having some calculations applied to it - use the Series operations -> Forecast analysis.

faq33

Here, you get one more column, compared to the Forecast tab available for the Series list, allowing you to specify the Missing value method. Then you can add here just the last forecast value and set i.e. Lineral interpolation for the missing values.

faq34


How do you remove forecast from a series?

Use this expression:

if(isForecast(series), Null(),series)

For example:

if(isForecast(imfweo_in_ngdpd), Null(), imfweo_in_ngdpd)



Forecast values are seen in a different color in Macrobond. If you only want to remove the forecast indicator from a series, use the expression:

FlagForecast(series, 0)

For example: Removing forecast indicator

FlagForecast(imfweo_in_ngdpd, 0)





For more information on forecast: Add a forecast



How do you change the language in a chart

Click outside of the chart area:

Presentation properties -> Format -> Language.

You can either use your current Windows settings or select language from the drop-down menu.



How to remove special style of forecasted values?

Each value in a time series has an indicator, flag, that tells if the value is a forecast or not. In a presentation, such values are often presented with a distinct style.

If you do not want to make a visual distinction of forecasted values, here are two ways you can accomplish that.

Go to the Series list and in the formula editor use the following formula: flagforecast i.e. flagforecast(brnaac1043, 0). “0”, used in this formula, turns off the forecast flag in the series.

Brazil_GDP_forecast

Go to Style sheet editor and find the Graph style section (on the far right). Here, in Forecast tab, set the selection of colors for forecasts to be identical as in the Graph tab (you can do this by changing the % of the opacity to 100). Make sure you make the changes for all properties: general, markers, border and candle stick – keeping any of them unchanged will prevent the graph to be displayed the way you want.

forecast style sheet


How do I determine a percentile value of a series?

Use the two following functions:

Percentile(series, percentile)

Percentile(series, percentile, window)

Examples:

Percentile(sek, 70)

Percentile(sek, 70, YearLength())



How to disable automatically adding new series into a chart?

Click on an empty chart area (i.e. below the X axis). Go to the Properties area and click the ‘More’ button in Chart elements box. Here, untick the option ‘Include new series automatically’.

FAQ601

Repeat these steps for each chart you want this rule to be applied to.



How do I set the Window length in the Macrobond formula language?

The window length is specified as the number of observations. To get the number of observations for a year, you can use the YearLength() and YearLengths(years) functions like this:

Mean(uscaes0046, YearsLength(5))

You can also calculate the moving average by adding a 'Smoothing' analysis and selecting 'Moving average'; this can be more convenient when you want to apply the same smoothing to many time series.



How do I define custom formula functions?

In order to define own formula function, go to the top menu of the Macrobond application and select Edit -> User defined functions.

faq4_4

The 'Function editor' window pops up. Here select the account where you want to store your new formula. It can be saved in the:

The main window allows you to define a function. A simple example could be:
.gdpru(x) = (x/rugdp)*100

This function calculates what % of the Russian GDP is covered by series x. In this case .gdpru is the formula’s name, and (x) is the parameter it takes in. After saving this formula, you can use it in the Series list by substituting x for any series. For example:
.gdpru(rugpfi0026)

More advanced formulas can be constructed using the built-in formulas, which can be selected from the list available on the right side of the window.

faq4_5


How do I extend a time series?

You can extend a series with its last value to the end of a specified period with one of the following functions:

ExtendToEndOfMonth(series)

ExtendToEndOfQuarter(series)

ExtendToEndOfYear(series)



If you want to mark the extended values as forecasts, use:

ExtendLastAsForecast(series)



If you need more specific point in time or value use:

Extend(series, observation, number)

which can be translated as Extend(what_series, how_far, with_what_value).

For example:

Extend(uscpi, Last(EndOfYearAhead(0)), LastValid(uscpi))

extends uscpi to the last day of the current year with its last valid value.

Extend(uscpi, Last(EndOfQuarterAhead(6)), LastValid(uscpi))

extends uscpi to the last day six quarters ahead with its last valid value.



How do I rearrange analyses in the Analysis tree?

Right-click on the analysis and choose “Move up”. It moves the analysis one place up, if you wish to move it up further you will need to perform the “Move up” action again. There is no option to move the analyses down.



How do you delete a document?

To delete a saved document permanently:

  1. Select the Open dialog (File -> Open)
  2. Navigate to the location where the document is saved
  3. Right-click on the document and select Delete, or select it and press Del on your keyboard.




Is Macrobond’s formula language case sensitive?

No.

Formula language



Visualizing data

Can I display multiple y-axes?

Macrobond only supports 2 y-axes, one on the left and another on the right-hand side of the chart.



For more information go here: How do you add an alternate axis to a chart



How do you add an alternate axis to the chart?



There are two ways to add an alternate axis to the chart:

Take note: this doesn’t duplicate the existing y-axis. To display two y-axis with identical values, see: How to add a second, mirrored axis to a chart



How do you add a second, mirrored axis to a chart?

Click on the y-axis, and go to Properties for Value axis -> Axis – > set Synchronize to Values

Take note: this disables the option of alternate axis function. To display two y-axes with different values, please refer to: How do you add an alternate axis to a chart



How do I change the Main title?

Double click on the title and enter your new title in the edit main title window that opens up.

If you do not have a title on your chart you should first enable it:

Properties of the chart -> Chart elements -> Main title

Click on “Edit” next to title to insert a title. In the new window, you can also add a dynamic text.



For more information on Dynamic text: Dynamix text





How do you remove (or increase) the gap between columns in a column chart?

After setting up your column chart, click on one of the columns. From the presentation properties you can adjust:







How do I remove grid lines?

Click anywhere within the chart area, switch to presentation properties -> grid lines. Untick horizontal or/and vertical options.



Can I scale all values in text column of Bar chart the same?

By default, each number in a bar chart column is scaled and formatted individually.

If you want to format a set of numbers as a group, you can use a named formatting group. For instance, you can set the default text to {s .Value @last:2}, which will define a number formatting group called "last" (you can pick any name).



How do you show legend labels on a chart?

Click in the chart area:

Presentation properties -> Elements

Tick the checkbox “Legend labels”

Each label will show the current description for series. If you want to change this, double-click on it and edit it with our Dynamic Text functionality or insert your own custom text.

Dynamic Text

Adding x-axis labels





How do you change a scatter chart from a connected line to dots?

Click on the line, switch to Presentation properties -> Appearance.

  1. Change the Graph style from Automatic to Custom
  2. Set Line to None
  3. Marker style to a chosen size of the marker (2px, 4px etc.).


How can I align several Macrobond charts on the same slide?

If you prefer your Macrobond charts to predictably fill in space in your PowerPoint presentation, you may want to use the placeholder functionality.

A little bit of preparations need to be done before creating your presentation.

  1. Please open new presentation in PowerPoint and go to View -> Slide Master.
  2. Insert new Layout by clicking Insert Layout or please right at the end of list and select Insert Layout
  3. Click on the Insert placeholder button and select type of the placeholder you want to use. In this case Picture or Chart might be the best fit. Click in any place under Title style to add a placeholder.
  4. Adjust placeholder by removing its title. After adjusting the size of the area you would like to put your chart into, the safest option is to copy-paste first placeholder and place it in desired order. In our case, we have 3 placeholders.
  5. Now you can close the Master view and start creating your presentation. Please add layout prepared in previous steps from drop-down menu.
  6. Set a Chart view size in Macrobond, then copy the chart to PowerPoint
  7. Please paste copied chart into PowerPoint and click on it. Please cut it (Ctrl+X) and paste into the placeholder. If you omit this step, you will end up with just a picture, not a dynamic Macrobond chart.


How do you show value labels on the x-axis?

Click in the chart area:

Presentation properties -> Elements

Tick the checkbox ‘‘Horizontal axis value labels’’

Each label will show the last valid value by default. If you want to change this, please double-click on it and edit it with our Dynamic Text functionality.

Dynamic Text

Adding legend labels on a chart





How do you show value labels on the y-axis?

Click in the chart area:

Presentation properties -> Elements

Tick the checkbox ‘‘Vertical axis value labels’’

Each label will show the last value for each series. If you want to change this, double-click on it and edit it with our Dynamic Text functionality or insert your own custom text.

Dynamic Text

Adding legend labels on a chart





How do I remove borders from a chart?

Click anywhere in the chart area, switch to Presentation properties -> Border -> Width and set the width to “None”.



How do I highlight the latest observation on a Scatter chart?

There are two different ways to highlight last observation on a Scatter chart.

1. Using Observation label

a) go to chart annotations tab,
b) choose observation label and select any point,
c) then clear point in time field and press Enter.


2.Using Formula language

This works by turning the last valid value into a forecast. Forecast values can then be set to use a different color/shape. An example of this:

FlagForecast(fx:s1, Counter(fx:s1)=EndValid(fx:s1))

For more information on this:Highlighting forecast



How do I add recession bands?

Right-click in the "Chart area", select "Fill range…" (or hit CTRL+F on keyboard) click "Import…" button and choose the recession. After clicking "OK" you will be able to edit chosen recession bands (see: How do I edit recession bands?) Also, you can apply further recession band(s) to the chart if you wish.



How do I edit recession bands?

Tick custom box in the "Fill range" window and set your own color, opacity, start and end dates. You can click on an arrow to apply settings to all periods below the one you edited.

To delete a period, click on the period and hit "Delete" on keyboard.

To add a new period, click on the "Add" button and set start and end dates.

To move one recession period above or below the other drag it above or below the other in the "Fill range" window.



How do I create my own recession bands?

Right-click in the "Chart area", select "Fill range…" (or hit CTRL+F on keyboard) click on the "Add" button. Set your own start and end dates and adjust the color and opacity for the band. You can apply this recession band to the graph by clicking on the "OK" button or add further recession periods by clicking on the "Add" button.

You can save these recession bands (see: How do I save my custom created recession bands?)



How do I remove a recession from the chart?

To remove the recession period from chart right-click on "Chart area", select "Fill range…" (or hit CTRL+F on keyboard) then select the period you wish to remove and hit "Delete" on keyboard. You can select more than one period by holding down the CRTL key.



How do I save my custom created recession bands

In a chart where you have applied your custom recession bands, right-click in the "Chart area", select "Fill range…" (or hit CTRL+F on keyboard) then click "Export…" button, select the account where you want to save your custom recession periods, enter a name and click O"K".



How do I change the x-axis date intervals?

Click on the x-axis, switch to Presentation properties -> Labels. You can:



How do I reverse / invert a series?

To reverse the sign of a series use the following expression in the Series list:

-series

To invert a series, use the following expression in the Series list:

1/series

For example: 1/eur



For more information, go here: Reversing y-axis



How can I add series back to my chart?

Go to "Graph layout" and drag desired series to a graph.



How do I change the default font?

Invoke the properties of the chart settings by clicking below the legend on the x-axis. From the appearance box change the Default font family. Use the "..." button to pick one from the entire list of installed fonts.:



How do I add a regression line in a scatter chart?

Steps:

  1. Add a Regression analysis and select your explanatory and dependent series
  2. Add a Scatter chart under Regression
  3. Open the chart and navigate to Graph layout
  4. Add a Line graph
  5. Drag the explanatory series under it, alongside with predicted series.

Example file: Macrobond Chart Library: Example Regression line



How to reverse / invert y-axis?

To reverse the y-axis:

  1. please select it on your Time chart,
  2. then switch to: Presentation properties -> Scale
  3. then check “Reverse”.


For more information, go to: Reversing a series



How do I change the source on a chart?

Click outside of the chart area, switch to Presentation properties – > Chart elements.

Change copyright from “Automatic” (the default option is Macrobond) to “Custom” and click edit. A new window appears “Edit copyright text”, where you can put your preferred description. Additionally, you may want to use the dynamic text option, which is available on the right side under a tab called “Dynamic properties”. Here you can add for e.g. a source of the series ( {s .Source} )



For more information about Dynamic text go to: Dynamic text



How do I set a logarithmic scale?

Click on the y-axis, switch to Presentation properties -> Scale and tick the “Logarithmic” option.



How do I add custom chart sizes for presentation purposes?

In the Command bar, on the top of the screen, click the ‘Chart view size’ tab. In the displayed ‘View size’ box, tick the ‘Fixed size’ option, which uncovers the ‘Width’ and ‘Height’ fields. Here you can type in your custom chart dimensions.

faq1

As an option you can add them to the ‘Favorite sizes´ drop down list on the left. Saving them in the Company account (in the window which appears after clicking the 'Add...' button) allows you to share the defined sizes within your company.



Can I display series with different frequencies on the same chart?

A chart can only be displayed in one frequency. However, by setting the document frequency to be the highest available from the set of series used in the chart and then setting the Conversion settings to be “Linear interpolation” for the "To higher frequency conversion method" you will find the chart has the desired look.



How do I change the graph legend text?

In order to modify the graph's legend text for only one of the charts, double-click on the graph which legend text you would like to modify.

The Edit custom legend text window appears.

faq21

Here, remove the current text object and/or add the new one(s) from the ‘Dynamic properties’ table on the right and/or type in your own text instead. You can also use both together: your own text and the dynamic objects (i.e. adding a space or coma between the dynamic text objects).

faq22

For more information regarding changes within the dynamic text, please check https://help.macrobond.com/analyses/presentations/charts/dynamic-text/



How do I change the color of a series when it is below/above 0?

Use the following expressions:

if(series >0, series, Null())

if(series <0, series, Null())

The expressions above create two series – one with values above 0 and another with values below 0. Then you can change the color of lines by going to:

Presentation properties -> Appearance -> Graph style: Custom -> Color

Or use the expression:

if(series >0, series, flagforecast(series))

Using the If statement, we mark all values below 0 as a forecast. Then you can change the color of forecast by going to:

Presentation properties -> Appearance -> Graph style: Custom -> Forecast ->Color



How do I add chart annotations (horizontal/vertical lines and labels)?

In the Command bar, on the top of the screen, click the ‘Chart annotations’ tab. Using the options from the ‘Annotations’ box you are able to add the horizontal and vertical lines and the observation labels to your chart.

faq4

In order to modify the text of the observation label, click on it and use the options available in the properties boxes (on top of the screen).

faq42

Check also How do I change the graph legend text? in order to learn how to modify the dynamic text used in the observation labels.



How do I change/remove left axis/right axis (lhs, rhs) text?

Click on the chart area, outside its graph, axis and legend elements. As the result you should see the Chart properties' boxes displayed on the top of the screen (within the Presentation properties tab). Find the Format box and delete the ", lhs" and ", rhs" text from the dialog boxes below (here you can click the X appearing after removing the text to bring back the default text).

faq5


How do I remove a legend text?

You have the option of hiding the entire chart legend or just some of its elements.

In order to hide the entire legend, click on the legend area. Go to the Properties boxes (located on top of the screen, within the Presentation properties tab) and find the Content box. Here untick the 'Show legend' option.

faq61

In order to bring the legend back to your chart, click below the X axis, go to the Properties boxes and find the Charts element one. Here tick the 'Show legend' option.

faq62

In order to hide one or more graph text from the legend, click on the graph line which legend’s description you would like to hide. Go to the Properties boxes and find the Content box. Here find the 'Graph text' drop down menu and select 'Hidden'.

faq63


How do I hide analysis texts like (c.o.p 12 months)?

If you use the legend located on the bottom of the chart area, click on the legend area. As the result you should see the Legend properties' boxes displayed on the top of the screen (within the Presentation properties tab). Find the Content box and untick the 'Show analysis text' option.

faq7

If you use only the legend labels (legend text attached to the graphs), click below the X axis, go to the Properties boxes and find the Charts element one. Here tick the 'Show legend' option. Next, find the Content box in the Chart Properties and untick the 'Show analysis text' and 'Show legend' options.



How do I create stacked bars?

Having your chart displayed in the Analytics activity, select Graph layout icon or press Ctrl+L.

FAQ101

Next, change the graph type clicking on the ‘arrow’ icon, next to the current graph, and select a ‘Stacked column’ from the drop down list.

FAQ102

Alternatively, select the input time series and press the ‘Stack column’ graph type button from the main menu or drag the time series to the Graphs list to change their order.

FAQ103


How do I add a horizontal line, for example a 0-line, to my chart?

Switch to Chart annotations tab and select here the ‘Horizontal line’ option.

FAQ201

You can either manually set the position of the line on your graph or, going to the Line box in the Properties area,

FAQ202

type the exact position number in the ‘Position’ field. Additionally, upon ticking the ‘Custom style’ option, you can adjust here the width and the color of the line.



How do I add a text box and observation labels to a chart?

Select the Chart annotations tab and go to the Chart ornaments and Annotations boxes. Here you can select the following: 'Text box', 'Line', 'Arrow', 'Observation label', 'Horizontal' and 'Vertical lines', to be added to your chart.

FAQ301


How do I add a text to a Text box in my chart?

Double click on your Text box. The ‘Edit ornament text’ window pops up where you can write your text.

FAQ401


How can I use an alternate scale for one of my graphs?

Click on the graph of the time series, for which you would like to display a secondary axis. In the Properties area, go to the Content box and tick the ‘Use alternate scale’ option. You can see how to set this option in our video tutorial: Adding an alternate scale to a chart.

Please note that adding the alternate scale is possible only when axis synchronization is set to ‘none’ - this is a default setting. If you wish to change it, choose one of the axis of your chart (it does not matter which one), go to Presentation properties and then change the ‘Synchronize’ option (placed under ‘Axis’) to ‘Grid’ or 'Values'.

FAQ501


How do I create, save and apply a new style sheet?

In order to create a new style sheet, open the Style sheets activity and click the '+'.

faq3_1

To save the new style sheet, go to the Style sheet main menu and select 'Save chart style sheet as…'. Or, if you have created a new document, just click 'Ctrl+S' buttons.

faq3_2

To apply the newly created style sheet to a chart in the Analytics activity, open the Analytics activity, go to the Style sheet main menu, look for and select the name of the newly created style sheet.

faq3_3


How do I change the color of the graph area?

In the Analytics activity:

Click on the graph area, and go to the Appearance properties box on the top of the screen. Here you can select the color from the drop-down list or apply the RGB color model.

faq3_4_new

If you want to re-use the selected background color, you can save it in a new style sheet, to be picked up later from the Style sheet main menu.

In the Style sheet activity:

Click on the graph area and go to the Appearance properties box for the Graph area. Here - as above - you can select the color from the drop-down list or apply the RGB color model.

faq3_5


How do I change the color and the font of the chart’s main title?

In the Analytics activity:

Click on the title and go to the Appearance properties box. Here you can change both: the text color and its font.

faq3_6_new

If you want to re-use the selected title's color and font, you can save it in a new style sheet, to be picked up later from the Style sheet main menu.

In the Style sheet activity:

Click on the title and go to the Appearance properties box. Here - as above - you can change both: the text color and its font.

faq3_7


How do I change the color of the x and y axis text?

In the Analytics activity:

Click on the x or y axis text and go to the Appearance properties box. Here, among other properties, you can change its color.

faq3_8_new

If you want to re-use the selected x and/or y axis color, you can save it in a new style sheet, to be picked up later from the Style sheet main menu.

In the Style sheet activity:

Click on the x or y axis text and go to the Appearance properties box. Here - as above - among other properties, you can change its color.

faq3_9


How do I change the graph's color?

In the Analytics activity:

Click on the graph and and go to the Appearance properties box. Here you can change the graph's color.

faq3_10_new

If you want to re-use the selected graph's color, you can save it in a new style sheet, to be picked up later from the Style sheet main menu.

In the Style sheet activity:

Go to the Graph styles area and for each of the graphs, change its properties in the Graph tab, using the General section for their colors.

faq3_11_new


Using data and charts in other applications

How do I upload a time series, that starts before 1900, to Excel?

Macrobond supports the time series with dates starting before 1900, while Excel, unfortunately, does not. Macrobond Add-in automatically recognizes dates which are entered as text strings. To construct them use the =CONCATENATE formula in Excel.

Set aside one column, and type in the start date’s year.

faq81

Type in the CONCATENATE formula. Compose the date according to your regional settings, i.e.

faq82

Paste in the values in the next column. The rest of the dates will be filled in according to template’s frequency.

faq83


Can I create dynamic tables in MS Office using Macrobond data?

It is possible starting from 1.16, using Bar charts. You can create dynamic tables, which can be also exported to MS Office products.

Tables with last observation, change since last observation and annual rate can be created automatically.

To do that, please:

  1. Add series you would like to observe in a table form; in Browse or Analytics.
  2. Switch to Presentation and select Table, last value, c.o.p.
  3. When the resulting table is created, you can move it to Analytics with Open in Analytics icon (A)
  4. From there, you can export this chart to Excel/PowerPoint/Word

To do that manually / add different calculations, please:

  1. Add a Scalar analysis with desired calculations
  2. Add a Bar chart under Scalar, then go to Graph layout.
  3. Delete the Graph chart group and add 3 new Text columns.
  4. Drag each calculation under text column
  5. Click OK.
  6. Click on one of the cells in a newly-created column and switch to Cell properties
  7. Click on “Edit column default…” and select Value from Dynamic texts.
  8. Repeat steps 6 & 7 for each column
  9. Export chart to Excel/PowerPoint/Word


How do I copy a chart to a Microsoft Office document?

Right-click on your chart and select 'Copy' option (as an alternative you can also use the combination of 'Ctrl+C' buttons).

faq3_12_new

Open your MS document and paste the chart.

You are able to update all your charts in an MS Office document by clicking on Macrobond tab in the main menu and clicking on 'Refresh (...)' button.

faq3_13_new

You can also update only one of the charts in your MS Office document by right-clicking on it and selecting Macrobond document Object -> Refresh.

faq3_14_new

The copied chart is NOT connected to the main Macrobond document in any way. Changes in its layout and data can be made via the MS document by double clicking on the chart. It opens the chart in the Macrobond application (in the Analytics activity), in a separate tab, indicating its source MS document in the tab's title.

faq3_15_new

For more information regarding copying charts to Microsoft Office documents, please check the Copying Macrobond charts to Microsoft Office documents video tutorial.



How do I select to what account a series is uploaded to from Excel?

In order to change an account for your uploaded time series from i.e. personal account to a company one:

If you need to change an account for several time series, you can use the replace option in Excel (keyboard combination of Ctrl+H):

Once you finish replacing, click on the Upload All Templates option from the menu.

faq3_18


How do I find the metadata to be used in MbndMeta Macrobond Excel formula function?

MbndMeta(series, name) returns the metadata identified by the parameter "name" for the time series. You can use the Macrobond application to find out what metadata is available for the particular time series, you are currently working with, and how they can be referenced.

Let’s take the usgdp series as an example.

FAQ4_1 FAQ4_2 FAQ4_3


When I try using Bloomberg codes, I see “unknown prefix: ih:bl”. How do I fix this?

This error indicates that Bloomberg connector is not enabled. To enable the Bloomberg connector, go to:

Edit -> Settings -> My series: tick the checkbox “Enable Bloomberg connector”.

This will ensure that ih:bl: codes will be parsed as Bloomberg expressions.



How to ensure the quality of charts when converting to PDF? (4 ways)
Saving charts from within an Office document

Go to File->Save as or File->Export in Office 2010 and later.

Select PDF from the list of available file formats.

This action uses the built-in PDF converter in Office provided by Microsoft, which is the optimal means of conversion for Macrobond charts.

Depending on the document content or program settings results may differ for users. If you are not satisfied with the PDF, try the options below.

Using Microsoft Print to PDF

Alternatively, If you are using Windows 10, you can use a function called Microsoft Print to PDF.

This is a conversion tool built into the operating system that you select from the print menu in any program that allows you to print (not only Office programs).

NOTE: Using this option does not mean that you will automatically print the document on paper, but rather that you are “printing” a PDF version of the original document to save.

Adjusting line color and transparency if using Adobe Acrobat

If the lines are more pronounced in the PDF than you like, try changing the color and/or transparency.

Due to default Adobe Acrobat settings, this will be more effective than adjusting line thickness. Adobe Acrobat automatically 'enhances' very thin lines. This is especially applicable to dotted/dashed lines.

If you do not want to adjust color or transparency in your charts this feature can be turned off:

Edit->Preferences->Page Display->Rendering->Enhance thin lines

However, your PDF conversions are likely to look better when the setting is on as per the default.

Modifying the size of the chart before exporting

This action is explained in one of our screencasts (view from 1:40).



Technical information

Can I change my password?

Our security policy does not allow changing the password; we can only reset it and generate a new one. If you need to change it, please send an email to Support



How do I check which version of Macrobond is installed?

Select Help-> About.

Your version will be displayed in a pop-up window.



How do I check if I'm using a 32-bit or a 64-bit version of Macrobond?

Select Help-> About.If you have a 64-bit version, it will be specified in the window:

Your version will be displayed in a pop-up window.

If there is no indication after the version number you are using a 32-bit version.



How do I log out from Macrobond?

There is no “log-off” option in Macrobond.

In most cases, there’s no need to do that, since by default, Macrobond can be used on two machines. We set the lock to the first two computers that log-in – the lock can be later reset if, for example, you’re moving to a different machine. In which case please let us know and we’ll reset the account.



Can I run Macrobond on a Mac?

Macrobond is not an OS X application. It’s designed to work on Windows. It is possible to run it on a Mac hardware, but it requires additional software and a Windows license. Since the solutions vary, we cannot provide support, although issues on a well-setup virtual machine, for example with Parallels, are rare.



Can I run Macrobond on Linux?

There is no Linux version of Macrobond.

Please refer to the requirements section from: Deploying the Macrobond application



Can I decide to print a chart from MB in color or grayscale from within the application?

No, Macrobond uses the default settings from the printer.

To circumvent this, you can create several ‘printer profiles’ in Windows for the same printer device. Each printer can have different settings, for example you can create a printer ‘HP LaserJet Black and white’.



How do I use the automatic proxy configuration tool to find the right method of connecting to the Macrobond servers?

Some network setups are restrictive about what connections are allowed – in certain cases the default settings used by Macrobond may not work. If this is the case, our built-in Automatic Configuration tool may help with finding the right connection.

To access it, choose Edit from the main menu bar, and then go to Settings:

1

A new window pops up – switch here to the “Communication” tab and click the Automated configuration button.

2

Pressing “Run tests” will automatically test several popular communication options. This might take a few minutes.

3

If a working configuration is found, a new button should appear above the “Run test” button – “Use the first configuration that was found”. Clicking it will configure the Macrobond application to work with a configuration that has been detected as the most optimal one.

4

In order to start using the new configuration, press “OK“ and restart the application.



How to re-enable add-ins in Microsoft Office?

Excel 2016

Open Excel and select File from the main menu:

Next, select Options from the list.

Select Add-Ins from the menu and go to the Manage drop-down list. Here select the Disabled Items option and press the "Go..." button.

If your add-in is disabled, it will be displayed on the list – select the "macrobond excel add-in" and click the "Enable" button.

After restarting Excel, the Macrobond tab should be available again.


Excel 2013

Open Excel and select File from the main menu:

10

Next, select Options from the list.

11

Select Add-Ins from the menu and go to the Manage drop-down list. Here select the Disabled Items option and press the "Go..." button.

12

If your add-in is disabled, it will be displayed on the list – select the "macrobond excel add-in" and click the "Enable" button.

13

After restarting Excel, the Macrobond tab should be available again.


Excel 2010

Open Excel and go to the Excel Options ribbon:

8

Select Add-Ins from the menu and go to the Manage drop-down list. Here select the Disabled Items option and press the "Go..." button.

9

A new window pops up. Here select the "macrobond excel add-in" and click the "Enable" button.

7

Press "OK". After restarting Excel, the Macrobond tab should be available again.


Excel 2007

Open Excel and go to the Excel Options ribbon:

5

Select Add-ins from the menu and go to the Manage drop-down list. Here select the Disabled Items option and press the "Go..." button.

6

A new window pops up. Here select the "macrobond excel add-in" and click the "Enable" button.

7

Press "OK". After restarting Excel, the Macrobond tab should be available again.


Word 2007

Open Word and go to the Word Options ribbon:

14

Select Add-Ins from the menu and go to the Manage drop-down list. Here select the Disabled Items option and press the "Go..." button.

A new window pops up. Here select the "macrobond office add-in" and click the "Enable" button.

16

Press "OK". After restarting Word, the Macrobond tab should be available again.


PowerPoint 2007

Open PowerPoint and go to the PowerPoint Options ribbon:

17

Select Add-Ins from the menu and go to the Manage drop-down list. Here select the Disabled Items option and press the "Go..." button.

18

A new window pops up. Here select the "macrobond office add-in" and click the "Enable" button.

16

Press "OK". After restarting PowerPoint, the Macrobond tab should be available again.