How to calculate a monthly end-of-period series?

Pipelines are an easy way to apply transformations to datasets that update automatically every time data is updated.

Suppose we have a daily data series for which we need only the last value of each month. It is possible to do that in Excel. For example, an auxiliary column is added that is TRUE / FALSE depending on whether or not we are on the last day of the month, and then a pivot table or a filter is created, or another table and a vlookup. EOMONTH, for example, is used for that.

Doing it with pipelines basically you don't have to do anything. The transformation is done by itself

Step 1. Find the original dataset

Suppose we want a monthly exchange rate series for all countries. Something like this exists in this BIS dataset but with daily frequency.

"Financial - Global - BIS - Main Currencies"

Step 2. Create the pipeline and choose the source

With the source dataset identified we are going to create a pipeline (with Engine v2.0). As the first option in "Fetch Dataset" we select the dataset that we found in the previous step

image.png

image.png

Step 3. Resample the frequency and choose the variable and publish to a dataset

Third, a step is added to perform a "Frequency Resample" and monthly is chosen as the destination frequency and "Last" as the information aggregation formula. By adding a new step we can then choose where we want that information to be saved.

image.png

Related insights