# Introduction

Macrobond offers an API that can be used in Python for Windows. You can download time series (values, dates, and metadata) and upload your own series to the in-house database which can then be used in the Macrobond application and optionally shared with others.

# Requirements

The Macrobond API has been tested with Python 2.7.11 and 3.9.5 which can be downloaded here as well as Anaconda 4.3.1 with Python 3.6 that can be found here. This is a COM API and requires the pywin32 extension found here. We have tested with pywin32 b301.

If you are running the 64-bit version of Python, you need to use the 64-bit version of Macrobond. The 32-bit version of Python will work both with 32 and 64-bit installations of Macrobond.

Note that due to an issue with pywin32 b220 you must not use the "makepy" or win32com.client.gencache.EnsureModule features of pywin32 on the type of the Macrobond API.

This API can be used only with Legacy (without searching and revision history functions) or Data+ license.

# Getting started

We also recommend installing special package with Macrobond constants. For more information about it see Class with constants.

# Working with Python API

A time series is represented by the ISeries interface. A time series consists of a vector of values, a calendar, and a set of metadata.

The vector of values consists of floating-point values. A missing value is represented by a NaN. To test if a value is a NaN in Python, you use the method math.isnan(value). To represent a NaN you can use float('nan') or math.nan (Python 3.5 and later). Note! You must never use these constants to compare if a value is NaN; always use math.isnan(value) to test if a value is NaN.

A missing value typically means that a value was expected, but it is missing for some reason. When you do a unified series request, you have the option of specifying what method, if any, that should be used to fill in missing values.

The calendar is exposed as lists of dates as well as a couple of methods to go between index in the list of values and a date. Dates are represented as timestamps with time zone UTC. Each time series has a frequency and a start date. Daily series also have information about what weekdays that are covered. In addition to this, certain dates can be skipped.  This is mostly used for daily series when there is a holiday or other day when the market is closed. It is not common, but you may find skipped dates in series of other frequencies too. For instance, some Chinese monthly data is not reported for February and this month is then skipped.

Series are represented by objects that implement the ISeries interface. This interface has properties and methods to retrieve values, dates and metadata.

• Download raw data. All available data will be made available without any conversion. You do this by calling FetchOneSeries or FetchSeries with one or more series names (strings).
• Download unified data. You can specify frequency, currency, date range, missing value, and frequency conversion methods. When more than one series is downloaded, they will all have the same calendar. This means that they will have equal length, the same frequency and that a specific index in the vector or values corresponds to the same point in time for all the series. This type of download is made by creating a request object by calling CreateUnifiedSeriesRequest().

## Interfaces

The documentation of the interfaces uses a 'c' style syntax which includes the type of information for parameters and return values.

The special type VARIANT is used when several data types are accepted which is then explained in the text.

[] is used to denote a list. For instance, ISeries[] is a list of ISeries objects.

In the examples below a class called constants defines all the constants used in the example. For more information about it see Class with constants paragraph.

### IConnection

All interaction with the Macrobond starts with an instance of the Connection object which is access through the IConnection interface. You create an instance of the object like this:

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")

The interface contains the following methods and properties:

 IDatabase Database This property returns a reference to the database interface. Close() Call this method if you want to free all resources used by the Macrobond API. Opening and closing sessions can be slow, so it is usually not a good idea to open and close them for each request. int[] Version Returns an array of three values for the version of the installed API. For example, for the version 1.23.3 it will return [1, 23, 3].

### Time series - codes

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database
s = d.FetchOneSeries("usgdp")

values = s.Values
print(values)

We store values in their original form, in single units, i.e., 19699465000000. While our main app transforms these values automatically on Time table/Time chart, Python download them in original form, without any formatting. You can scale them by yourself with below code. For scaling with pandas see Download one series, create data frame and scale values.

import win32com.client

c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database
s = d.FetchOneSeries("usgdp")

values = s.Values
values2 = tuple(val/1000000000000 for val in values)
print(values2)

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

s = d.FetchOneSeries("#PerCapita(usgdp)")

values = s.Values
print(values)

This will download the entire time series usgdp and uscpi without any transformations. It is faster to download several series in one call rather than one by one.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

listOfSeries = d.FetchSeries(["usgdp", "uscpi"])
seriesUsgdp = listOfSeries[0]
seriesUscpi = listOfSeries[1]

values = seriesUsgdp.Values
print(values)

#### Download several series, transform them to a common length and calendar

By default, the highest frequency of the series and the union on the ranges will be used. For more examples, see the documentation about the ISeriesRequest.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

r = d.CreateUnifiedSeriesRequest()

listOfSeries = d.FetchSeries(r)
seriesUsgdp = listOfSeries[0]
seriesUscpi = listOfSeries[1]

values = seriesUsgdp.Values
print(values)

This will download two series and convert them to the higher frequency. The data range is set to start year 2020 and continue until the last observation that has data in both series. You can also set relative date like "-5y" or "-10" (observations). For more information see ISeriesRequest.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

r = d.CreateUnifiedSeriesRequest()
r.StartDate = "2020-01-01"

listOfSeries = d.FetchSeries(r)
seriesUsgdp = listOfSeries[0]
seriesUscpi = listOfSeries[1]

values = seriesUsgdp.Values
print(values)

For all available 'CalendarDate' methods see CalendarDateMode.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

from macrobond_api_constants import CalendarDateMode as cd

r = d.CreateUnifiedSeriesRequest()

#set the start to the first point where data is available in all series
r.StartDateMode = cd.ALL_SERIES
#set the end to the last point where data is available in all series
r.EndDateMode = cd.ALL_SERIES

listOfSeries = d.FetchSeries(r)
seriesUsgdp = listOfSeries[0]
seriesUscpi = listOfSeries[1]

values = seriesUsgdp.Values
print(values)

For all available 'Frequency' methods see SeriesFrequency.
For all available 'MissingValue' methods see SeriesMissingValueMethod.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

from macrobond_api_constants import SeriesFrequency as f
from macrobond_api_constants import SeriesMissingValueMethod as mv

r = d.CreateUnifiedSeriesRequest()
r.Frequency = f.MONTHLY

listOfSeries = d.FetchSeries(r)
seriesUsgdp = listOfSeries[0]
seriesUscpi = listOfSeries[1]

values = seriesUsgdp.Values
print(values)

For all available 'Frequency' methods see SeriesFrequency.
For all available 'ToHigher' methods see SeriesToHigherFrequencyMethod.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

from macrobond_api_constants import SeriesFrequency as f
from macrobond_api_constants import SeriesToHigherFrequencyMethod as th

r = d.CreateUnifiedSeriesRequest()
r.Frequency = f.WEEKLY

listOfSeries = d.FetchSeries(r)
seriesUsgdp = listOfSeries[0]
seriesUscpi = listOfSeries[1]

values = seriesUsgdp.Values
print(values)

#### Download series, convert frequency with 'to lower frequency' method and set one currency

For all available 'Frequency' methods see SeriesFrequency.
For all available 'ToLower' methods see SeriesToLowerFrequencyMethod.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

from macrobond_api_constants import SeriesFrequency as f
from macrobond_api_constants import SeriesToLowerFrequencyMethod as tl

r = d.CreateUnifiedSeriesRequest()
r.Frequency = f.ANNUAL
r.Currency = "CHF"

listOfSeries = d.FetchSeries(r)
seriesUsgdp = listOfSeries[0]
seriesChgdp = listOfSeries[1]

values = seriesUsgdp.Values
print(values)

For all available 'Frequency' methods see SeriesFrequency.
For all available 'PartialPeriods' methods see SeriesPartialPeriodsMethod.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

from macrobond_api_constants import SeriesFrequency as f
from macrobond_api_constants import SeriesPartialPeriodsMethod as pp

r = d.CreateUnifiedSeriesRequest()
r.Frequency = f.QUARTERLY

listOfSeries = d.FetchSeries(r)
seriesUscpi = listOfSeries[0]

values = seriesUscpi.Values
print(values)

For all available 'CalendarMode' methods see CalendarMergeMode.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

from macrobond_api_constants import CalendarMergeMode as cm

r = d.CreateUnifiedSeriesRequest()

#all holidays will always be included as missing values instead of being skipped in the calendar
r.CalendarMergeMode = cm.FULL_CALENDAR

listOfSeries = d.FetchSeries(r)
seriesUs = listOfSeries[0]
seriesEur = listOfSeries[1]

values = seriesUs.Values
print(values)

#### Get values and dates

This will download the time series usgdp and the list of values as well as a list of the dates corresponding to each value.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

s = d.FetchOneSeries("usgdp")
values = s.Values
dates = s.DatesAtStartOfPeriod

print(values)
print(dates)

#### Create and upload a monthly series

This creates a monthly time series. Since just one data is specified, this will be the start date of the series and the rest of the dates will be implicitly calculated based on the frequency. Series will be stored as Account in-house. This example uses our constants package. For more information about it see here.

For all available 'Frequency' methods see SeriesFrequency.
For all available 'Weekdays' methods see SeriesWeekdays.

import win32com.client
import datetime

c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

from macrobond_api_constants import SeriesFrequency as f
from macrobond_api_constants import SeriesWeekdays as wk

startDate = datetime.datetime(1990, 1, 1, tzinfo=datetime.timezone.utc)
values = [12.2, 12.7, 12.8, 13.0]
s = d.CreateSeriesObject("ih:mb:priv:s1", "My forecast", "us", "Forecasts", f.MONTHLY, wk.MONDAY_TO_FRIDAY, startDate, values, m)

d.UploadOneOrMoreSeries(s)

#### Create and upload a daily series, specify each date

This will create a daily time series. In this case we have chosen to specify the date for each observation. Please note that the 5th and 6th of January 1980 are Saturday, Sunday, and will not be included in the series since it is set to use Monday-Friday. The 7th is no included in the list of dates, so this date will be skipped in the calendar. Series will be stored as Account in-house. This example uses constants package. For more information see here.

For all available 'Frequency' methods see SeriesFrequency.
For all available 'Weekdays' methods see SeriesWeekdays.

import win32com.client
import datetime

c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

from macrobond_api_constants import SeriesFrequency as f
from macrobond_api_constants import SeriesWeekdays as wk

dates = [datetime.datetime(1980, 1, 2, tzinfo=datetime.timezone.utc),
datetime.datetime(1980, 1, 3, tzinfo=datetime.timezone.utc),
datetime.datetime(1980, 1, 4, tzinfo=datetime.timezone.utc),
datetime.datetime(1980, 1, 8, tzinfo=datetime.timezone.utc)]
values = [12.2, 12.7, 12.8, 13.0]
s = d.CreateSeriesObject("ih:mb:priv:s2", "My forecast", "us", "Forecasts", f.DAILY, wk.MONDAY_TO_FRIDAY, dates, values, m)

d.UploadOneOrMoreSeries(s)

Series will be stored as Account in-house. This example uses constants package. For more information see here.

For all available 'Frequency' methods see SeriesFrequency.
For all available 'Weekdays' methods see SeriesWeekdays.

import win32com.client
import datetime

c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

from macrobond_api_constants import SeriesFrequency as f
from macrobond_api_constants import SeriesWeekdays as wk

startDate = datetime.datetime(1980, 1, 1, tzinfo=datetime.timezone.utc)
values = [12.2, 12.7, 12.8, 13.0]
s = d.CreateSeriesObject("ih:mb:priv:s1", "My forecast", "us", "Forecasts", f.DAILY, wk.MONDAY_TO_FRIDAY, startDate, values, m)

d.UploadOneOrMoreSeries(s)

#### Create and upload a monthly series with flagged forecast

Series will be stored as Account in-house. This example uses our constants package. For more information about it see here.

For all available 'Frequency' methods see SeriesFrequency.
For all available 'Weekdays' methods see SeriesWeekdays.

import win32com.client
import datetime

c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

from macrobond_api_constants import SeriesFrequency as f
from macrobond_api_constants import SeriesWeekdays as wk

startDate = datetime.datetime(2022, 11, 1, tzinfo=datetime.timezone.utc)
values = [12.2, 12.7, 12.8, 13.0]
forecastFlags = [False, False, True, True]
s = d.CreateSeriesObjectWithForecastFlags("ih:mb:priv:s1", "My forecast", "us", "Forecasts", f.MONTHLY, wk.MONDAY_TO_FRIDAY, startDate, values, forecastFlags, m)

d.UploadOneOrMoreSeries(s)

#### Delete a series

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

d.DeleteOneOrMoreSeries("ih:mb:priv:s1")

### IDatabase

This interface allows you to interact with the Macrobond database.

### ISeries

This interface represents a Macrobond time series.

 string Name The name of the series. bool IsError If True, then the series request resulted in an error and the ErrorMessage property contains an error message. If there is an error, only these two properties are valid. string ErrorMessage Contains an error message if IsError is True. string Title The title of the series. IMetadata Metadata Metadata information for the time series. double[] Values A list of all values in the time series. date[] DatesAtStartOfPeriod A list of dates. There is one date for the start of the period for each value in Values. date[] DatesAtEndOfPeriod A list of dates. There is one date for the end of the period for each value in Values. bool[] ForecastFlags A list flags where a value is True if the corresponding value in Values is a forecast. date StartDate The date of the first observation of the time series. date EndDate The date of the last observation of the time series. SeriesFrequency Frequency The series frequency. SeriesWeekdays Weekdays A bit field of weekdays to use for daily time series. double GetValueAtDate(date d) Get the value at or preceding a specific date. int GetIndexAtDate(date d) Get the zero-based index of the value at or preceding the specified date in the Value list. double TypicalObservationCountPerYear The typical number of observations per year based on the frequency of the series. IMetadata[] ValuesMetadata Returns a list of metadata for each value in Values. The list will be empty if there is no metadata for any value. If the list is not empty, if will be an object of type IMetadata or None if no metadata is available for that specific value.

### ISeriesRequest

Use this interface to set up a request of unified series. You create objects with this interface by calling IDatabase.CreateUnifiedSeriesRequest(). Then you configure the object and pass it as a parameter to IDatabase.FetchSeries().

 ISeriesExpression AddSeries(string name) Add a series to the list of series to request. You can optionally use the returned interface to do further configurations, such as methods for missing value and frequency conversion. ISeriesExpression [] AddedSeries A list of the added series. SeriesFrequency Frequency The frequency that all series will be converted to. The default value is ‘Highest’, which means that all series will be converted to the same frequency as the series with the highest frequency. CalendarMergeMode CalendarMergeMode Determines how to handle points in time that are not available in one or more series. The default value is ‘AvailibleInAny’ SeriesWeekdays Weekdays This determines what days of the week that are used when the resulting frequency is Daily and the CalendarMergeMode is ‘FullCalendar’. string Currency The currency code based on the three letter IS 4217 codes that will be used to convert any series expressed in currency units. The default is an empty string, which means that no currency conversion will be done. There is a list of the supported currencies on a Currencies List web page. CalendarDateMode StartDateMode Determines if the automatic start of the series is at the first point when there is data in any series or the first point where there is data in all series. The default is ‘DataInAnySeries’. This setting is not used when the StartDate property is set to an absolute point in time. VARIANT StartDate This specifies the start date used for all the series in the request. The value can be empty, a specific date or a string representing a point in time as described below. If it is empty or a relative reference, the StartDateMode will be used to determine the start. CalendarDateMode EndDateMode Determines if the automatic end of the series is at the last point when there is data in any series or the last point where there is data in all series. The default is ‘DataInAnySeries’. This setting is not used when the EndDate property is set to an absolute point in time. VARIANT EndDate This specifies the end date used for all the series in the request. The value can be empty, a specific date or a string representing a point in time as described below. If it is empty or a relative reference, the EndDateMode will be used to determine the end.

For the StartDate and EndDate properties, you can specify a string representing a point in time. This can either be an absolute reference on the form 'yyyy', 'yyyy-mm' or 'yyyy-mm-dd' or a reference relative the last observation of the series based on these examples:

 -50 Fifty observations before the last available observation. +40 Forty observations after the last available observation. -2y Two years before the last available observation. -1q One quarter before the last available observation. -4m Four months before the last available observation. -3w Three weeks before the last available observation. -5d Five days before the last available observation.

### ISeriesExpression

Use this interface to set properties for a series requested by calling ISeriesRequest.AddSeries().

 string Name The name of the series SeriesMissingValueMethod MissingValueMethod The method to use when filling in any missing values in this series. The default is ‘Auto’. SeriesToLowerFrequencyMethod ToLowerFrequencyMethod The method to use when converting this series to a lower frequency. The default is ‘Auto’. SeriesToHigherFrequencyMethod ToHigherFrequencyMethod The method to use when converting this series to a higher frequency. The default is ‘Auto’. SeriesPartialPeriodsMethod PartialPeriodsMethod The method to use for partial periods at the ends of the series when converting to a lower frequency. The default is None.

#### How to see series metadata?

List metadata available for a series.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

s = d.FetchOneSeries("usgdp")

print(m)

#### How to see specific metadata?

To see other metadata replace text in last line's quotation marks:

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

s = d.FetchOneSeries("usgdp")

print(metadata)

All possible commands can be found under IMetadataInformation.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

v = m.ListAllValues()

for timedim in v:
print(timedim.Value) #list of versions of this metadata

#### Get series title

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

s = d.FetchOneSeries("usgdp")
t = s.Title

print(t)

#### Get the presentation text for a region code

This will get information about the metadata type called 'Region' and then get the presentation text for the region called 'us'. The result will be the string 'United States'.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

description = m.GetValuePresentationText("us")

print(description)

#### Get currency metadata from a series

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

s = d.FetchOneSeries("segdp")

currencyCode = m.GetFirstValue("Currency")

print(currencyCode)

#### Get a list of all currencies

This will get information about the metadata type called 'Currency' and list all available currency codes.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

v = m.ListAllValues()

for currency in v:
print(currency.Value)

#### Get name of release

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

s = d.FetchOneSeries("usgdp")

print(releaseName)

#### Get the next release time

The NextReleaseEventTime attribute contains information about the next scheduled release of updated series. The date might be missing if no release date is known.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

s = d.FetchOneSeries("usgdp")

if (releaseName is not None):
r = d.FetchOneEntity(releaseName)

print(nextReleaseTime)

#### Get next publication date

Some series have 'NextReleaseEventTime' metadata but some don't. Then it's the best to use 'CalendarUrl' metadata connected to Release metadata.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

s = d.FetchOneSeries("usgdp")

if (releaseName is not None):
r = d.FetchOneEntity(releaseName)

print(calendarUrl)

#### How to know if series is discontinued?

EntityState = 0 means series is active
EntityState = 4 means series is discontinued

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

r = d.CreateUnifiedSeriesRequest()

for series in d.FetchSeries(r):
print(series.Metadata.GetFirstValue("EntityState"))

#### How to handle double region?

Some series have two regions and they do not display properly when you ask for Region metadata.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

s = d.FetchOneSeries("eea_8_579")

cc = m.GetValues("Region")
print(cc) #get shortenings for double regions i.e., ('ch', 'city_ch_lug')

print(regions.GetValuePresentationText(cc)) #get full names for double regions i.e., Switzerland, Lugano

This interface represents a set of metadata for a time series or entity.

Metadata is represented by a name and a value. The type of the value can be a string, a boolean, an integer or a date. An examples of a metadata names are 'Region' and 'Currency.' Some metadata can have multiple values, like Region, but others, Currency, can have only one value.

 VARIANT GetFirstValue(string name) Get the first metadata value with the specified name. VARIANT[] GetValues(string name) Get a list of all the values for a specified metadata name. VARIANT[,] ListNames() Get a list of all the metadata specified together with a description for each name. bool IsReadonly Indicating whether new values can be added or hidden in this instance. Typically, only newly created instances of the metadata objects can be edited and only until they have been used in a series. AddValue(string name, VARIANT value) Add a value to the metadata instance. The value can be a single value or a list of values. HideValue(string name) Hide a value inherited from a parent metadata collection.

 Region The region code based on the two letter ISO 3166 codes. There is a list of the supported regions on a Regions List web page. Currency The currency code based on the three letter ISO 4217 codes. There is a list of the supported currencies on a Currencies List web page. DisplayUnit The unit of the time series. Class The class is a string with one of the values Stock or Flow. This determines how the automatic frequency conversion in Macrobond works. If the value is Flow, the data will be aggregated when converted to a lower frequency and distributed over the period when converted to a higher frequency. ForecastCutoffDate All observations at this date and later will flagged as forecasts. The date must not be before the start or after the end of the series. MaturityDate Some analyses, such as the Yield curve analysis, can use this information to automatically configure the maturity length. If you set this value, you should also set RateMethod but not set the MaturityDays value. MaturityDays This value is a positive integer that some analyses, such as the Yield curve analysis, can use this information to automatically configure the maturity length. If you set this value, you should also set RateMethod but not set the MaturityDate value. 1 week is 7 days, 1 month is 30 days and one year is 360 days. RateMethod This value should be either simple or effective that some analyses, such as the Yield curve analysis, can use this information for automatically configuration. If you set this value, you should also set one of the MaturityDate or MaturityLength values. IHCategory This is a text describing the category for an in-house series. This can be set when uploading series. It will be used together with Region to group series in the Macrobond application when browsing the in-house database. IHInfo This is an optional comment for an in-house series. This can be set when uploading series and can be seen in the Series information report in the Macrobond application. LastModifiedTimeStamp The time when this entity was last modified. For time series, this includes changes in either values or metadata. LastReleaseEventTime This value can be available for entities of type Release. It contains the time of the previous scheduled update. NextReleaseEventTime This value can be available for entities of type Release. It contains the time of the next scheduled update.

 VARIANT Value The value of the metadata string Description The description of the value. string Comment A comment about the value.

### IEntity

This interface represents a Macrobond entity. There are many types of entities in the Macrobond database. Examples are Source, Release, Company and Security. Time series are also entities.

 string Name The name of the entity. bool IsError If True, then the entity request resulted in an error and the ErrorMessage property contains an error message. If there is an error, only these two properties are valid. string ErrorMessage Contains an error message if IsError is True. string Title The title of the entity. IMetadata Metadata Metadata information for the entity.

### Concept/Region key - codes

Some of our series are connected with each other through metadata called Region Key. If you know it, you can find series for other countries/regions. In main-app you can see those data sets under Concept & Region data tree.

#### Get 'concept' from a series

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

s = d.FetchOneSeries("uscpi")

#get the 'concept' this series is associated with
if cpiKey == None:
print("No RegionKey defined")
else:
print(cpiKey)

#### Get 'concept' description

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

s = d.FetchOneSeries("uscpi")

#get the 'concept' this series is associated with
if cpiKey == None:
print("No RegionKey defined")
else:
print(cpiKey)

#get description of 'concept'

print(keyMetaInfo.GetValuePresentationText(cpiKey))

### Search - codes

You can pass more than one search filter to IDatabase.Search(filters). In this case the filters have to use different entity types and searches will be nested so that the result of the previous filter will be used as a condition in the subsequent filter linked by the entity type.

#### Search for 'concept' based on a series, download whole data set

This method requires a Data+ license.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

s = d.FetchOneSeries("ted_ar_ahw")
cpiKey = s.Metadata.GetFirstValue("RegionKey") #get the 'concept' of a series

query = d.CreateSearchQuery()
query.SetEntityTypeFilter("TimeSeries")
query.AddAttributeValueFilter("RegionKey", cpiKey) #create search query based on 'concept'

result = d.Search(query).Entities

for s in result:
print(s.Name)

#### Search for specific time series defined by the 'concept' and download them

This method requires a Data+ license.

This example searches for time series that are defined by Macrobond as the GDP series for the regions 'us', 'gb' and 'cn'. The RegionKey attribute defines what is called a 'concept' and is used to build the 'Concept & Category' database view in Macrobond Application. It is a powerful way to identify corresponding series for many regions. For the GDP series, the RegionKey is 'gdp_total.'

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

query = d.CreateSearchQuery()
query.SetEntityTypeFilter("TimeSeries")

result = d.Search(query).Entities

for s in result:
print(s.Name)

#### Search for series of a specific industry sector

This method requires MB 1.24 (and higher) and a Data+ license.

This will select the Companies where IcbClassification=icb2713 (Aerospace).

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

query = d.CreateSearchQuery()
query.SetEntityTypeFilter("Company")

result = d.Search(query).Entities

for s in result:
print(s.Name)

#### Search for series closing prices and traded volume for companies of a specific industry sector

This method requires MB 1.24 (and higher) and a Data+ license.

This example uses list of search queries. The first query will select the Companies where IcbClassification=icb2713 (Aerospace).

The second query (querySecurity) will find all securities where CompanyKey=main_equity and has the Company equal to one of the companies in the result of the first query.

The query called queryCloseSeries will find all series where DataType=close and has the Security equal to one of the securities found by the previous filter. The volume series are found in an analogous way.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

query = d.CreateSearchQuery()
query.SetEntityTypeFilter("Company")

querySecurity = d.CreateSearchQuery()
querySecurity.SetEntityTypeFilter("Security")

queryCloseSeries = d.CreateSearchQuery()
queryCloseSeries.SetEntityTypeFilter("TimeSeries")

querySeriesVolume = d.CreateSearchQuery()
querySeriesVolume.SetEntityTypeFilter("TimeSeries")

resultClose = d.Search([query, querySecurity, queryCloseSeries]).Entities
resultVolume = d.Search([query, querySecurity, querySeriesVolume]).Entities

for s in resultClose:
print(s.Name)

for s in resultVolume:
print(s.Name)

### ISearchQuery

Use this interface to set up a search query. You create objects with this interface by calling IDatabase.CreateSearchQuery(). Then you configure the object and pass it as a parameter to IDatabase.Search().

### ISearchResult

This interface represents a search result returned by a call to IDatabase.Search(query).

 IEntity[] Entities The list of entities returned by the search. bool IsTruncated This is True if the result is not exhaustive. There is a limit on the number of entities that can be returned. It is possible to get at least 2000 entities before the result is truncated.

### Revision/Vintage - codes

#### Get all available revisions

This example only works with Data+ license.

Below example uses pandas - for more information on how to use it see Pandas.

import win32com.client
import pandas as pd

c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

#Helper method
def toPandasSeries(series):
#For some reason, pandas 0.19 does not like the result when to_datetime
#is used directly on the array DatesAtStartOfPeriod. Convert to string first.
pdates = pd.to_datetime([d.strftime('%Y-%m-%d') for d in series.DatesAtStartOfPeriod])
return pd.Series(series.values, index=pdates)

s = d.FetchOneSeriesWithRevisions("uscpi")
history = s.GetCompleteHistory()

full_history = pd.DataFrame()

for x in range(13, len(history)):

print(full_history)


#### Get the timestamps of each value in the current series

This example only works with MB 1.24 (and higher) and a Data+ license.

This example fetches the revision history for a series and obtains a series with the current vintage. The values will be the same as the 'head' series, but you will also get the ValuesMetadata with a timestamp of when each value was last modified.

import win32com.client
import datetime

def getRevisionTimestamp(m):
if m == None:
return(None)
t = m.GetFirstValue("RevisionTimestamp")
return (t)

c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

s = d.FetchOneSeriesWithRevisions("uscpi")
currentVintageSeries = s.GetVintage(datetime.datetime.now())
lastChangedTimestamps = [getRevisionTimestamp(vm) for vm in currentVintageSeries.ValuesMetadata]

for t in lastChangedTimestamps:
print(t)

#### Get revision history for original and first release

This example only works with Data+ license.

This example fetches the revision history for a series and obtains a series with the original release values and one with the values of the first revision.

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

s = d.FetchOneSeriesWithRevisions("uscpi")

firstReleaseSeries = s.GetNthRelease(0)
firstRevisionSeries = s.GetNthRelease(1)

values = firstRevisionSeries.Values
print(values)

#### Get the timestamps of each value in Nth release

This example only works with Data+ license.

This example fetches the revision history for a series and obtains a series with the original release values. It then creates a list of the timestamps when the values were recorded.

import win32com.client

def getRevisionTimestamp(m):
if m == None:
return(None)
t = m.GetFirstValue("RevisionTimestamp")
return (t)

c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

s = d.FetchOneSeriesWithRevisions("uscpi")
firstReleaseSeries = s.GetNthRelease(0)
firstReleaseDates = [getRevisionTimestamp(vm) for vm in firstReleaseSeries.ValuesMetadata]

for t in firstReleaseDates:
print(t)


### ISeriesWithRevisions

This example only works with Data+ license.

This interface represents a time series with revision history.

The series returned by Head, GetNthRelease, GetVintage and GetCompleteHistory will all be of equal length and share the same calendar. In this way they can easily be used in a data frame.

 bool IsError If True, then the series request resulted in an error and the ErrorMessage property contains an error message. If there is an error, only these two properties are valid. string ErrorMessage Contains an error message if IsError is True. bool HasRevisions If True, then the series has one or more revisions. bool StoresRevisions If True, then the series stores revision history. This can be True while HasRevisions is False if no revisions have yet been recorded. datetime TimeOfLastRevision The timestamp of the last revision. ISeries Head The current revision of the time series. ISeries GetNthRelease(int n) Get then nth revision of each value. GetNthRelease(0) will return the first release of each value. Values that have not been revised the number of times specified and value for which the complete revision history is not known will be represented by NaN. The series ValuesMetadata will contain the metadata attribute RevisionTimeStamp with the timestamp when the revision was made to that value. ISeries GetVintage(datetime time) Get the series as it looked at the time specified. The metadata attribute RevisionTimeStamp will contain the timestamp of when this revision was recorded. In order to make this series of the same length as the Head series, it may be padded with NaN. ISeries GetObservationHistory(date d) Get a daily series with the revision history of the value specified by the date. ISeries[] GetCompleteHistory() Get list of all revisions. Each time series has the metadata attribute RevisionTimeStamp with the timestamp the revision. The first series typically has date 1899-12-30 which means that the timestamp of the original data is not known. Please note that this can potentially be a lot of data and use a large amount of memory. datetime[] GetVintageDates() Get a list of timestamps representing when revisions were recorded.

## Class with constants

The class below contains the values of all constants for the API as used in the preceding examples.

You can also find these constants in the PIP package macrobond-api-constants which can be found here. To define constant in a code please use after import win32com.client for example:

from macrobond_api_constants import SeriesFrequency as f

and then you can call in code:

r = d.CreateUnifiedSeriesRequest()
r.Frequency = f.MONTHLY

### How to define the lowest frequency with last value?

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

from macrobond_api_constants import SeriesFrequency as f
from macrobond_api_constants import SeriesToLowerFrequencyMethod as l

r = d.CreateUnifiedSeriesRequest()
r.Frequency = f.LOWEST
r.StartDate = "2000"

### How to define average monthly values?

import win32com.client
c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

from macrobond_api_constants import SeriesToLowerFrequencyMethod as l
from macrobond_api_constants import CalendarDateMode as cd
from macrobond_api_constants import SeriesFrequency as f

r = d.CreateUnifiedSeriesRequest()
r.StartDateMode = cd.ALL_SERIES
r.Frequency = f.MONTHLY

listOfSeries = d.FetchSeries(r)
values = listOfSeries[0].Values
dates = listOfSeries[0].DatesAtStartOfPeriod

### SeriesFrequency

This enumeration contains the supported time series frequencies.

 ANNUAL Once a year. (int = 1) SEMI_ANNUAL Twice a year. (int = 2) QUAD_MONTHLY Once in 4 months. (int = 3) QUARTERLY Once in 3 months (int = 4) BI_MONTHLY Every second month. (int = 5) MONTHLY Once a month. (int = 6) WEEKLY Once a week (int = 7) DAILY Once a day. (int = 8) LOWEST When specified in a series request, this corresponds to the lowest frequency of the series in set. (int = 100) HIGHEST When specified in a series request, this corresponds to the highest frequency of the series in the request. (int = 101)

### SeriesWeekdays

This enumeration contains flags that are used as bitmasks to determine what weekdays that are used in a daily time series.

 SUNDAY (int = 1) MONDAY (int = 2) TUESDAY (int = 4) WEDNESDAY (int = 8) THURSDAY (int = 16) FRIDAY (int = 32) SATURDAY (int = 64) FULLWEEK Sun+Mon+Tue+Wed+Thu+Fri+Sat (int = 127) MONDAY_TO_FRIDAY Mon+Tue+Wed+Thu+Fri (int = 62) SATURDAY_TO_THURSDAY Sun+Mon+Tue+Wed+Thu+Sat (int = 95) SATURDAY_TO_WEDNESDAY Sun+Mon+Tue+Wed+Sat (int = 79) SUNDAY_TO_THURSDAY Sun+Mon+Tue+Wed+Thu (int = 31) MONDAY_TO_THURSDAY_AND_SATURDAY Sun+Mon+Tue+Wed+Thu+Sat (int = 94 )

### CalendarMergeMode

This enumeration defines how calendars are merged into one calendar when there are time periods missing in one or more series. Please note that missing time period is not the same thing as a missing value. A time period is missing in cases when there should be no observation such as on a holiday. A missing value, on the other hand, is a special value in the time series that indicates that there should have been a value, but it is unknown for some reason.

 FULL_CALENDAR Use all the time periods as specified by the frequency and weekdays. (int = 0) AVAILABLE_IN_ALL Use the time periods that are available in all series. (int = 1) AVAILABLE_IN_ANY Use the time periods that are available in any series. (int = 2)

### CalendarDateMode

This enumeration defines how the StartDate and EndDate properties of a unified series request are interpreted when they are set to a relative or empty reference.

 ANY_SERIES Use the first or last time period where there is valid data in any series. (int = 0) ALL_SERIES Use the first or last time period where there is valid data in all series. (int = 1)

### SeriesMissingValueMethod

This enumeration determines the method for filling in any missing values.

 NONE Do not fill in missing values. They will remain NaN in the value vector. (int = 0) AUTO Determine the method based on the series classification. (int = 1) PREVIOUS Use the previous non-missing value. (int = 2) ZERO Use the value zero. (int = 3) LINEAR_INTERPOLATION Do a linear interpolation between the previous and next non-missing values. (int = 4)

### SeriesToLowerFrequencyMethod

This enumeration determines the method for converting a series to a lower frequency.

 AUTO Determine the method based on the series classification. (int = 0) LAST Use the last value of the time period. (int = 1) FIRST Use the first value of the time period. (int = 2) FLOW Aggregate the values of the time period. (int = 3) PERCENTAGE_CHANGE Aggregate the percentage change over the period. (int = 4) HIGHEST Use the highest value in the time period. (int = 5) LOWEST Use the lowest value of the time period. (int = 6) AVERAGE Use the average value of the period. (int = 7) CONDITIONAL_PERCENTAGE_CHANGE Aggregate percentage change of the period depending on series properties. (int = 8)

### SeriesToHigherFrequencyMethod

This enumeration determines the method for converting a series to a higher frequency.

 AUTO Determine the method based on the series classification. (int = 0) SAME Use the same value for the whole period. (int = 1) DISTRIBUTE Use the first value of the time period. (int = 2) PERCENTAGE_CHANGE Distribute the percentage change over the period. (int = 3) LINEAR_INTERPOLATION Use a linear interpolation of the values from this to the next period. (int = 4) PULSE Use the value for the first value of the period. (int = 5) QUADRATIC_DISTRIBUTION Use quadratic interpolation to distribute the value over the period. (int = 6) CUBIC_INTERPOLATION Use a cubic interpolation of the values from this to the next period. (int = 7) CONDITIONAL_PERCENTAGE_CHANGE Aggregate percentage change of the period depending on series properties. (int = 8)

### SeriesPartialPeriodsMethod

This enumeration determines the method for converting a series with partial periods.

 NONE Do not include partial periods. (int = 0) AUTO Determine the method based on the series meta data. (int = 1) REPEAT_LAST Fill up the partial period by repeating the last value. (int = 3) FLOW_CURRENT_SUM Fill up the partial period with the average of the incomplete period. (int = 3) PAST_RATE_OF_CHANGE Use the rate of change from the previous year to extend the partial period. (int = 4) ZERO Fill up the partial period with zeroes. (int = 5)

The type of a metadata value. These values correspond to the COM VARIANT data types.

 INT A 32-bit signed integer. VT_I4. (int = 3) DOUBLE A 64-bit floating-point number. VT_R8. (int = 5) DATE A date. VT_DATE. (int = 7) STRING A unicode string. VT_BSTR. (int = 8) BOOL A boolean value where -1 is True and 0 is False. VT_BOOL. (int = 11)

## Pandas - examples how to use them with the Macrobond API

### Helper method and defined options

For each use of pandas we recommend to add our Helper method and one of the three options visible below:

#Helper method
def toPandasSeries(series):
#For some reason, pandas 0.19 does not like the result when to_datetime
#is used directly on the array DatesAtStartOfPeriod. Convert to string first.
pdates = pd.to_datetime([d.strftime('%Y-%m-%d') for d in series.DatesAtStartOfPeriod])
return pd.Series(series.values, index=pdates)
#OPTION 1 - Get one series from Macrobond as a pandas time series
def getOneSeries(db, name):

#OPTION 2 - Get several series from Macrobond as pandas time series
def getSeries(db, names):
series = db.FetchSeries(names)
return [toPandasSeries(s) for s in series]

#OPTION 3 - Get several series from Macrobond as a pandas data frame with a common calendar
def getDataframe(db, unifiedSeriesRequest):
series = db.FetchSeries(unifiedSeriesRequest)
return pd.DataFrame({s.Name: toPandasSeries(s) for s in series})

import pandas as pd
import matplotlib.pyplot as plt
import win32com.client

#Helper method
def toPandasSeries(series):
#For some reason, pandas 0.19 does not like the result when to_datetime
#is used directly on the array DatesAtStartOfPeriod. Convert to string first.
pdates = pd.to_datetime([d.strftime('%Y-%m-%d') for d in series.DatesAtStartOfPeriod])
return pd.Series(series.values, index=pdates)
#OPTION 1 - Get one series from Macrobond as a pandas time series
def getOneSeries(db, name):

c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

series = getOneSeries(d, 'uscpi')
print(series)

import pandas as pd
import matplotlib.pyplot as plt
import win32com.client

#Helper method
def toPandasSeries(series):
#For some reason, pandas 0.19 does not like the result when to_datetime
#is used directly on the array DatesAtStartOfPeriod. Convert to string first.
pdates = pd.to_datetime([d.strftime('%Y-%m-%d') for d in series.DatesAtStartOfPeriod])
return pd.Series(series.values, index=pdates)

#OPTION 2 - Get several series from Macrobond as pandas time series
def getSeries(db, names):
series = db.FetchSeries(names)
return [toPandasSeries(s) for s in series]

c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

two_series = getSeries(d, ('uscpi', 'secpi'))
print (two_series)

import pandas as pd
import matplotlib.pyplot as plt
import win32com.client

#Helper method
def toPandasSeries(series):
#For some reason, pandas 0.19 does not like the result when to_datetime
#is used directly on the array DatesAtStartOfPeriod. Convert to string first.
pdates = pd.to_datetime([d.strftime('%Y-%m-%d') for d in series.DatesAtStartOfPeriod])
return pd.Series(series.values, index=pdates)

#OPTION 3 - Get several series from Macrobond as a pandas data frame with a common calendar
def getDataframe(db, unifiedSeriesRequest):
series = db.FetchSeries(unifiedSeriesRequest)
return pd.DataFrame({s.Name: toPandasSeries(s) for s in series})

c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

r = d.CreateUnifiedSeriesRequest()

frames = getDataframe(d, r)

frames.plot()
plt.show()

We store values in their original form, in single units, i.e., 19699465000000. While our main app transforms these values automatically on Time table/Time chart, Python download them in original form, without any formatting. You can scale them by yourself with below code. For scaling without pandas see Download one series and scale values.

import win32com.client
import pandas as pd

#Helper method
def toPandasSeries(series):
#For some reason, pandas 0.19 does not like the result when to_datetime
#is used directly on the array DatesAtStartOfPeriod. Convert to string first.
pdates = pd.to_datetime([d.strftime('%Y-%m-%d') for d in series.DatesAtEndOfPeriod])
return pd.Series(series.values, index=pdates)

#OPTION 3 - Get several series from Macrobond as a pandas data frame with a common calendar
def getDataframe(db, unifiedSeriesRequest):
series = db.FetchSeries(unifiedSeriesRequest)
return pd.DataFrame({s.Name: toPandasSeries(s) for s in series})

c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

r = d.CreateUnifiedSeriesRequest()

frames = getDataframe(d, r)
print(frames/1000000000000)


Outcome:
2022-03-31 19.727918
2022-06-30 19.699465

### Download one series, change frequency & currency, keep dates at the end of period, create data frame

The End of Period is defined inside the Helper method (series.DatesAtEndOfPeriod]). This example uses our constants package. For more information about it see here.

For all available 'Frequency' methods see SeriesFrequency.
For all available 'ToLower' methods see SeriesToLowerFrequencyMethod.

import win32com.client
import pandas as pd

from macrobond_api_constants import SeriesFrequency as f
from macrobond_api_constants import SeriesToLowerFrequencyMethod as tl

#Helper method
def toPandasSeries(series):
#For some reason, pandas 0.19 does not like the result when to_datetime
#is used directly on the array DatesAtStartOfPeriod. Convert to string first.
pdates = pd.to_datetime([d.strftime('%Y-%m-%d') for d in series.DatesAtEndOfPeriod])
return pd.Series(series.values, index=pdates)

#OPTION 3 - Get several series from Macrobond as a pandas data frame with a common calendar
def getDataframe(db, unifiedSeriesRequest):
series = db.FetchSeries(unifiedSeriesRequest)
return pd.DataFrame({s.Name: toPandasSeries(s) for s in series})

c = win32com.client.Dispatch("Macrobond.Connection")
d = c.Database

r = d.CreateUnifiedSeriesRequest()