Luciano Cohan
- 2 minutes to read

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 dataset and click "Transform Data"

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"

With the source, dataset identified click "Transform Data" on the dataset view

image.png

You will now be in the Pipelines Editor

image.png

Step 2. Choose the step "Resample frequency"

A step is added to perform a "Frequency Resample". The source data is daily - this will be automatically detected - and then you can select the new frequency - monthly, quarterly o yearly.

Once the frequency is selected you have to choose how you will aggregate daily data into a single value. Average? sum? Min o Max? In this example we will choose the "Last" value of the month..

image.png

image.png

Step 3. Results

Related insights

  • luciano
    - 2 minutes to read

    Read more... Excel and Google Sheets allow adding data from different sources. Here you can find an alternative way to embed data into Excel, by using our TSV data source:

    From a Dataset

    First of all, filter the information you want to use. Excel and Google Sheet limit the information that can be downloaded

  • luciano
    - 1 minute to read

    Read more...

    How to convert a series to the official USD or Blue Chip Swap?

    The pipeline engine "Apply Transform" step incorporates a new transformation that allows changing the source unit: Convert to dollar official or to Blue Chip Swap (for Argentina only).

    The pipeline is separated into Two steps

    1. Select ("Fetch") the dataset and its columns
    2. "Apply transform"
  • luciano
    - 1 minute to read

    Read more...

    How is a Time Series seasonally adjusted?

    Removing seasonality from time series is always complicated and laborious. The standard deseasonalization method is X-13ARIMA-SEATS or some other version of the methodologies maintained by the United States Census Bureau. Denationalizing usually includes using some application such as Eviews, Demetra or Stata or Python, combining it with the files that are downloaded

  • luciano
    - 3 minutes to read

    Read more...

    How to merge the content of two datasets?

    Surely in your usual work with data, you needed to join several data sources and if your calculation tool is Excel you may solve it with some combination of the VLOOKUP, HLOOKUP, and/or MATCH formulas. Excel is a great solution in many cases, but it can be difficult in some scenarios.