- Built-in formulas
- ExtendLinear() vs LinearExtend()
- In-app features
- Growth/Increase by % - custom methods
- Extend backward/backfill history
The following formulas, which you can type into the Series list, will extend the input series to the end of the corresponding period:
ExtendToEndOfMonth(series) ExtendToEndOfQuarter(series) ExtendToEndOfYear(series)
If you want to mark the extended values as forecasts, use:
Note series will be extended till the end of the common calendar (established at the last step before using this formula) which depends on the series included in document.
Using the formula below, you can customize what value is used and until when the series is extended.
Extend(series, observation, number)
It can be translated as Extend(which series, how far, which value) and in order to work effectively you will need helper formulas like Last() or Endvalid(), for example:
Extend(uscpi, Last(EndOfYearAhead(0)), LastValid(uscpi))
will extend uscpi series to end of current year with its last value. While:
Extend(usrate0001, Endvalid(usrate0001)+Monthslength(2), 0.75)
will extend series usrate0001 and will put 0.75 value two months from its end. Observations between end of series and new point will be filled in automatically.
Below formula extends the series using the % YoY change to calculate a forecast for the whole calendar range. New values will be marked automatically as forecast.
This one has an additional parameter to point to the end of extending range.
Note series will be extended till the end of the common calendar (established at the last step before using this formula) which depends on the series included in document. But here you can use same method with EndValid() as in previous example:
Both formulas, ExtendLinear() and LinearExtend(), are used to extend the series. With similar names and purpose, they can be easily confused but they extend the series in different way.
ExtendLinear uses linear interpolation to extend the series between the latest value and a manually specified value in the future, thus extending the series with a trend.
LinearExtend uses the Least-Square method to create a fitted line, which in turn is used to extend the series to a specified point in the future.
You can also extend one series with another. For that we have:
It can be translated as Join(older series, newer series). After last value of series1 Macrobond will add values from series2 from the respective points in time. This can also be modified by third parameter which will point exactly where to connect series':
Join(series1, series2, Start(series2)) Join(series1, series2, Date(yyyy, mm, dd))
We also have special join formula for appending historical values before the start of series1:
Unlike the standard Join formula this one translates to Join(newer series, older series). It takes all observations of series1 and adds all values of series2 that are before the start of series1.
For these formulas, a factor is applied to scale up the series:
JoinScaled(series1, series2) JoinScaledAppend(series1, series2) JoinMoreHistoryScaled(series1, series2)
JoinScaled() works like Join(). It will join series2 at the end of series1. At the date of the junction, series1 (older series) is scaled to match value of series2 (newer series). The rest of the values of series1 are then adjusted using the factor used at the date of the junction.
JoinScaledAppend() is similar to Join() and JoinScaled(). It will connect series at the end of series1, but it will scale series2 (newer series) to match series1 (older series).
JoinMoreHistoryScaled() works like JoinMoreHistory(). It will add more history to series1 (newer series) using the values of series2 (older series) that are before the start of series1. At the date of the junction, series2 (older series) is scaled to be equal to the value of series1 (newer series). The rest of the values of series2 are then adjusted using the factor used at the date of the junction.
Join formula can connect only two series. If you need to connect more, please insert one join formula into another:
Join(series1, Join(series2, series3))
Let's say you have series presenting values for different periods of time (i.e.; 2024, 2025, 2026 etc.) but they all start in 01/01/2023. The best way is not to use join() but moves them with formula and add them with Cross section.
If series is for example Daily and you want to convert its frequency to Annual, you will not see value for a current year because there is no 12 months of data - incomplete periods are removed.
In such situations use Conversion settings' 'Partial Periods to lower frequency method'. It will let you choose your preferred setting how to handle incomplete periods and mark the outcome as forecast value.
You can add forecast to the raw time series, before any calculations are made through tab on Series list or after calculations through Forecast analysis.
Note that these forecasts will be added in the original frequency of the time series, even if the document uses another frequency.
You can add absolute values to a raw time series in the Series list, or after having applied some calculations to it. Values can be added manually or copied (vertically aligned) and pasted in.
When you have one series and you want to extend series1 based on YoY-series2 you can do it by adding values with each formula:
You can also extend series when future estimates are in more than one series, you just need a different approach:
Below example is for continuous growth rate of 5%:
and this one represents Linear growth rate of 2% over time:
AggregateProduct(Pow(1+0.02, 1/round(YearLength(), 0)))*100
Custom formula which calculate stable growth by applied level (i.e., 1 % is expressed as 0.01) till a point in the future. That point can be also set as relative for example from the end of original series.
.GrowthTillDate(series, endDate, level) = let .extended=cutstart( (extend(series, endDate, LastValid(series))) , end(series)) .lastWithMultipliers = (if(counter(.extended)>end(series), 1+level, if(counter(.extended)=end(series), series, 1))) in join(series, flagforecast(AggregateProduct(.lastWithMultipliers))) end
Custom formula which can calculate annual rate starting from any point in time of a series till any point in the future.
.GrowthByAnnualRate(baseSeries, annualRate, startDate, endDate) = let .rate = Pow(1+annualRate/100, 1/YearLength()) .base = At(baseSeries, startdate) .period = CutStart(Extend(.base, endDate, .base), startDate) in .period*Pow(.rate, Counter(.period)-startDate) end
Select a value - when series will hit this point formula will calculate growth factor. Next it will calculate future values based on this growth factor.
We recommend using Forecast - extend relatively. With this function your forecast will always be ahead of series. It has relative weekly, monthly, quarterly and yearly setting.
Series begins at Date() with value 0 and then increases 0.5/360 for every calendar day
if(Counter()>Date(2018, 04, 05), (Counter()-(Date(2018, 04, 05)))*(0.5/360), 0)
AggregateProduct(If (Counter()>Date(2018, 04, 05), 1+(0.5/360), 1))-1
joinmorehistory(lag(AggregateProduct(1.5)*100, 1), 100) 50% daily growth joinmorehistory(lag(AggregateProduct(1.2)*100, 1), 100) 20% daily growth joinmorehistory(lag(AggregateProduct(1.1)*100, 1), 100) 10% daily growth joinmorehistory(lag(AggregateProduct(Pow(2, 1/5))*100, 1), 100) double value every 5 days
This example uses helper series created with AggregateProduct() creating new values based on increasing '1' by X%. Values are created since a particular Date() and extended till the end of original series. Then outcome is Rebased relatively by original series.
Create an Account in-house and then use join() formula to connect older values with series. This is the best option we can recommend right now.
If another series has more historical values and you want to match its time frames, use formula:
join(0, series, Start(series))
Note it will add as much 0 values as there is points in time in common calendar. It won't add anything if all series start in the same moment.
Use below formula to add 100 at the start of series: