Fuat Sungur

Solutions Architect

In this blog post, I’ll walk you through how you can visualize MongoDB Atlas Database Audit Logs with MongoDB Atlas Charts.

Visualize MongoDB Atlas Database Audit Logs

## Introduction

In the [previous post](https://www.mongodb.com/developer/article/time-series-candlestick/), we learned how to group currency data based on given time intervals to generate candlestick charts to perform trend analysis. In this article, we’ll learn how the moving average can be calculated on time-series data.

Moving average is a well-known financial technical indicator that is commonly used either alone or in combination with other indicators. Additionally, the moving average is included as a parameter of other financial technical indicators like [MACD](https://www.investopedia.com/terms/m/macd.asp). The main reason for using this indicator is to smooth out the price updates to reflect recent price changes accordingly. There are many types of moving averages but here we’ll focus on two of them: Simple Moving Average ([SMA](https://www.investopedia.com/terms/s/sma.asp)) and Exponential Moving Average ([EMA](https://www.investopedia.com/terms/e/ema.asp)).

## Simple Moving Average (SMA)

This is the average price value of a currency/stock within a given period. 

Let’s calculate the SMA for the BTC-USD currency over the last three data intervals, including the current data. Remember that each stick in the candlestick chart represents five-minute intervals. Therefore, for every interval, we would look for the previous three intervals.

First we’ll group the BTC-USD currency data for five-minute intervals: 


```js
db.ticker.aggregate([
 {
   $match: {
     symbol: "BTC-USD",
   },
 },
 {
   $group: {
     _id: {
       symbol: "$symbol",
       time: {
         $dateTrunc: {
           date: "$time",
           unit: "minute",
           binSize: 5
         },
       },
     },
     high: { $max: "$price" },
     low: { $min: "$price" },
     open: { $first: "$price" },
     close: { $last: "$price" },
   },
 },
 {
   $sort: {
     "_id.time": 1,
   },
 },
]);
```

And, we will have the following candlestick chart:

![Candlestick chart](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/camdlestick3_e788070c14.png)

We have four metrics for each interval and we will choose the close price as the numeric value for our moving average calculation. We are only interested in `_id` (a nested field that includes the symbol and time information) and the close price. Therefore, since we are not interested in high, low, open prices for SMA calculation, we will exclude it from the aggregation pipeline with the [`$project`](https://docs.mongodb.com/manual/reference/operator/aggregation/project/) aggregation stage:

```js
{
   $project: {
     _id: 1,
     price: "$close",
   },
}
```

After we grouped and trimmed, we will have the following dataset:

```js
{"_id": {"time": ISODate("20210101T17:00:00"), "symbol" : "BTC-USD"}, "price": 35050}
{"_id": {"time": ISODate("20210101T17:05:00"), "symbol" : "BTC-USD"}, "price": 35170}
{"_id": {"time": ISODate("20210101T17:10:00"), "symbol" : "BTC-USD"}, "price": 35280}
{"_id": {"time": ISODate("20210101T17:15:00"), "symbol" : "BTC-USD"}, "price": 34910}
{"_id": {"time": ISODate("20210101T17:20:00"), "symbol" : "BTC-USD"}, "price": 35060}
{"_id": {"time": ISODate("20210101T17:25:00"), "symbol" : "BTC-USD"}, "price": 35150}
{"_id": {"time": ISODate("20210101T17:30:00"), "symbol" : "BTC-USD"}, "price": 35350}
```

Once we have the above dataset, we want to enrich our data with the simple moving average indicator as shown below. Every interval in every symbol will have one more field (sma) to represent the SMA indicator by including the current and last three intervals:

```js
{"_id": {"time": ISODate("20210101T17:00:00"), "symbol" : "BTC-USD"}, "price": 35050, "sma": ?}
{"_id": {"time": ISODate("20210101T17:05:00"), "symbol" : "BTC-USD"}, "price": 35170, "sma": ?}
{"_id": {"time": ISODate("20210101T17:10:00"), "symbol" : "BTC-USD"}, "price": 35280, "sma": ?}
{"_id": {"time": ISODate("20210101T17:15:00"), "symbol" : "BTC-USD"}, "price": 34910, "sma": ?}
{"_id": {"time": ISODate("20210101T17:20:00"), "symbol" : "BTC-USD"}, "price": 35060, "sma": ?}
{"_id": {"time": ISODate("20210101T17:25:00"), "symbol" : "BTC-USD"}, "price": 35150, "sma": ?}
{"_id": {"time": ISODate("20210101T17:30:00"), "symbol" : "BTC-USD"}, "price": 35350, "sma": ?}
```

How is it calculated? For the time, `17:00:00`, the calculation of SMA is very simple. Since we don’t have the three previous data points, we can take the existing price (35050) at that time as average. If we don’t have three previous data points, we can get all the available possible price information and divide by the number of price data. 

The harder part comes when we have more than three previous data points. If we have more than three previous data points, we need to remove the older ones. And, we have to keep doing this as we have more data for a single symbol. Therefore, we will calculate the average by considering only up to three previous data points. The below table represents the calculation step by step for every interval:

| Time | SMA Calculation for the window (3 previous + current data points) |
| --- | --- |
| 17:00:00 | 35050/1 |
| 17:05:00 | (35050+35170)/2 |
| 17:10:00 | (35050+35170+35280)/3 |
| 17:15:00 | (35050+35170+35280+34910)/4 |
| 17:20:00 | (35170+35280+34910+35060)/4 <br/>*oldest price data (35050) discarded from the calculation |
| 17:25:00 | (35280+34910+35060+35150)/4 <br/>*oldest price data (35170) discarded from the calculation |
| 17:30:00 | (34190+35060+35150+35350)/4 <br/>*oldest price data (35280) discarded from the calculation |

As you see above, the window for the average calculation is moving as we have more data. 

## Window Functions

Until now, we learned the theory of moving average calculation. How can we use MongoDB to do this calculation for all of the currencies?

MongoDB 5.0 introduced a new aggregation stage, [`$setWindowFields`](https://docs.mongodb.com/manual/reference/operator/aggregation/setWindowFields/), to perform operations on a specified range of documents (window) in the defined partitions. Because it also supports average calculation on a window through [`$avg`](https://docs.mongodb.com/manual/reference/operator/aggregation/avg/#use-in--setwindowfields-stage) operator, we can easily use it to calculate Simple Moving Average:

```js
{
   $setWindowFields: {
     partitionBy: "_id.symbol",
     sortBy: { "_id.time": 1 },
     output: {
       sma: {
         $avg: "$price",
         window: { documents: [-3, 0] },
       },
     },
   },
}

```

We chose the symbol field as partition key. For every currency, we have a partition, and each partition will have its own window to process that specific currency data. Therefore, when we’d like to process sequential data of a single currency, we will not mingle the other currency’s data.

After we set the partition field, we apply sorting to process the data in an ordered way. The partition field provides processing of single currency data together. However, we want to process data as ordered by time. As we see in how SMA is calculated on the paper, the order of the data matters and therefore, we need to specify the field for ordering. 

After partitions are set and sorted, then we can process the data for each partition. We generate one more field, “`sma`”, and we define the calculation method of this derived field. Here we set three things:

- The operator that is going to be executed (`$avg`).
- The field (`$price`) where the operator is going to be executed on.
- The boundaries of the window (`[-3,0]`).
- `[-3`: “start from 3 previous data points”.
- `0]`: “end up with including current data point”.
    - We can also set the second parameter of the window as “`current`” to include the current data point rather than giving numeric value.

Moving the window on the partitioned and sorted data will look like the following. For every symbol, we’ll have a partition, and all the records belonging to that partition will be sorted by the time information:

![Calculation process](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/process_65fbc35406.gif)

Then we will have the `sma` field calculated for every document in the input stream. You can apply [`$round`](https://docs.mongodb.com/manual/reference/operator/aggregation/round/) operator to trim to the specified decimal place in a [`$set`](https://docs.mongodb.com/manual/reference/operator/aggregation/set/) aggregation stage:

```js
{
   $set: {
     sma: { $round: ["$sma", 2] },
   },
}
```

If we bring all the aggregation stages together, we will end-up with this aggregation pipeline:

```js
db.ticker.aggregate([
 {
   $match: {
     symbol: "BTC-USD",
   },
 },
 {
   $group: {
     _id: {
       symbol: "$symbol",
       time: {
         $dateTrunc: {
           date: "$time",
           unit: "minute",
           binSize: 5,
         },
       },
     },
     high: { $max: "$price" },
     low: { $min: "$price" },
     open: { $first: "$price" },
     close: { $last: "$price" },
   },
 },
 {
   $sort: {
     "_id.time": 1,
   },
 },
 {
   $project: {
     _id: 1,
     price: "$close",
   },
 },
 {
   $setWindowFields: {
     partitionBy: "_id.symbol",
     sortBy: { "_id.time": 1 },
     output: {
       sma: {
         $avg: "$price",
         window: { documents: [-3, 0] },
       },
     },
   },
 },
 {
   $set: {
     sma: { $round: ["$sma", 2] },
   },
 },
]);
```

You may want to add more calculated fields with different options. For example, you can have two SMA calculations with different parameters. One of them could include the last three points as we have done already, and the other one could include the last 10 points, and you may want to compare both. Find the query below:

```js
{
   $setWindowFields: {
     partitionBy: "_id.symbol",
     sortBy: { "_id.time": 1 },
     output: {
       sma_3: {
         $avg: "$price",
         window: { documents: [-3, 0] },
       },
       sma_10: {
         $avg: "$price",
         window: { documents: [-10, 0] },
       },
     },
   },
 }

```

Here in the above code, we set two derived fields. The `sma_3` field represents the moving average for the last three data points, and the `sma_10` field represents the moving average for the 10 last data points. Furthermore, you can compare these two moving averages to take a position on the currency or use it for a parameter for your own technical indicator.

The below chart shows two moving average calculations. The line with blue color represents the simple moving average with the window `[-3,0]`. The line with the turquoise color represents the simple moving average with the window `[-10,0]`. As you can see, when the window is bigger, reaction to price change gets slower:

![Candlestick chart](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/camdlestick4_263a7049d5.png)

You can even enrich it further with the additional operations such as covariance, standard deviation, and so on. Check the full supported options [here](https://docs.mongodb.com/manual/reference/operator/aggregation/setWindowFields/#window-operators). We will cover the Exponential Moving Average here as an additional operation.

## Exponential Moving Average (EMA)

EMA is a kind of moving average. However, it weighs the recent data higher. In the calculation of the Simple Moving Average, we equally weight all the input parameters. However, in the Exponential Moving Average, based on the given parameter, recent data gets more important. Therefore, Exponential Moving Average reacts faster than Simple Moving Average to recent price updates within the similar size window.

[`$expMovingAvg`](https://docs.mongodb.com/manual/reference/operator/aggregation/expMovingAvg/) has been introduced in MongoDB 5.0. It takes two parameters: the field name that includes numeric value for the calculation, and [`N`](https://docs.mongodb.com/manual/reference/operator/aggregation/expMovingAvg/#std-label-expMovingAvg-N) or [`alpha`](https://docs.mongodb.com/manual/reference/operator/aggregation/expMovingAvg/#std-label-expMovingAvg-alpha) value. We’ll set the parameter `N` to specify how many previous data points need to be evaluated while calculating the moving average and therefore, recent records within the `N` data points will have more weight than the older data. You can refer to the documentation for more information:

```js
{
   $expMovingAvg: {
      input: "$price",
      N: 5
   }
}
```

In the below diagram, SMA is represented with the blue line and EMA is represented with the red line, and both are calculated by five recent data points. You can see how the Simple Moving Average reacts slower to the recent price updates than the Exponential Moving Average even though they both have the same records in the calculation:

![Candlestick chart](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/camdlestick5_c8e6c10e37.png)

## Conclusion

MongoDB 5.0, with the introduction of Windowing Function, makes calculations much easier over a window. There are many aggregation operators that can be executed over a window, and we have seen `$avg` and `$expMovingAvg` in this article. 

Here in the given examples, we set the window boundaries by including the positional documents. In other words, we start to include documents from three previous data points to current data point (`documents: [-3,0]`). You can also set a range of documents rather than defining position. 

For example, if the window is sorted by time, you can include the last 30 minutes of data (whatever number of documents you have) by specifying the range option as follows: `range: [-30,0], unit: "minute". `Now, we may have hundreds of documents in the window but we know that we only include the documents that are not older than 30 minutes than the current data.

You can also materialize the query output into another collection through [`$out`](https://docs.mongodb.com/manual/reference/operator/aggregation/out/) or [`$merge`](https://docs.mongodb.com/manual/reference/operator/aggregation/merge/) aggregation stages. And furthermore, you can enable [change streams](https://docs.mongodb.com/manual/changeStreams/) or [Database Triggers](https://docs.atlas.mongodb.com/triggers/) on the materialized view to automatically trigger buy/sell actions based on the result of technical indicator changes.

Time series collections part 2: How to calculate Simple Moving Average and Exponential Moving Average 



Currency Analysis with Time Series Collections #2 — Simple Moving Average and Exponential Moving Average Calculation

## Introduction

Technical analysis is a methodology used in finance to provide price forecasts for financial assets based on historical market data. 

When it comes to analyzing market data, you need a better toolset. You will have a good amount of data, hence storing, accessing, and fast processing of this data becomes harder.

The financial assets price data is an example of time-series data. MongoDB 5.0 comes with a few important features to facilitate time-series data processing:

- [Time Series Collections](https://docs.mongodb.com/manual/core/timeseries-collections/): This specialized MongoDB collection makes it incredibly simple to store and process time-series data with automatic bucketing capabilities.
- [New Aggregation Framework Date Operators](https://docs.mongodb.com/manual/release-notes/5.0/#aggregation): `$dateTrunc`, `$dateAdd`, `$dateTrunc`, and `$dateDiff`.
- [Window Functions](https://docs.mongodb.com/manual/reference/operator/aggregation/setWindowFields/#mongodb-pipeline-pipe.-setWindowFields): Performs operations on a specified span of documents in a collection, known as a window, and returns the results based on the chosen [window operator](https://docs.mongodb.com/manual/reference/operator/aggregation/setWindowFields/#window-operators).

This three-part series will explain how you can build a currency analysis platform where you can apply well-known financial analysis techniques such as [SMA](https://www.investopedia.com/terms/s/sma.asp), [EMA](https://www.investopedia.com/terms/e/ema.asp), [MACD](https://www.investopedia.com/terms/m/macd.asp), and [RSI](https://www.investopedia.com/terms/r/rsi.asp). While you can read through this article series and grasp the main concepts, you can also get your hands dirty and run the entire demo-toolkit by yourself. All the code is available in the [Github repository](https://github.com/afsungur/mongodb-currency-analysis).

## Data Model

We want to save the last price of every currency in MongoDB, in close to real time. Depending on the currency data provider, it can be millisecond level to minute level. We insert the data as we get it from the provider with the following simple data model:

```json
{
   "time": ISODate("20210701T13:00:01.343"),
   "symbol": "BTC-USD",
   "price": 33451.33
}
```

We only have three fields in MongoDB:

- `time` is the time information when the symbol information is received.
- `symbol` is the currency symbol such as "BTC-USD." There can be hundreds of different symbols. 
- `price` field is the numeric value which indicates the value of currency at the time.

## Data Source

[Coinbase](https://www.coinbase.com/), one of the biggest cryptocurrency exchange platforms, provides a [WebSocket API](https://docs.pro.coinbase.com/#websocket-feed) to consume real-time cryptocurrency price updates. We will connect to Coinbase through a WebSocket, retrieve the data in real-time, and insert it into MongoDB. In order to increase the efficiency of insert operations, we can apply bulk insert.

Even though our data source in this post is a cryptocurrency exchange, this article and the demo toolkit are applicable to any exchange platform that has time, symbol, and price information.

## Bucketing Design Pattern 

The MongoDB document model provides a lot of flexibility in how you model data. That flexibility is incredibly powerful, but that power needs to be harnessed in terms of your application’s data access patterns; [schema design](https://www.mongodb.com/developer/article/mongodb-schema-design-best-practices/) in MongoDB has a tremendous impact on the performance of your application.

The [bucketing design pattern](https://www.mongodb.com/blog/post/building-with-patterns-the-bucket-pattern) is one MongoDB design pattern that groups raw data from multiple documents into one document rather than keeping separate documents for each and every raw piece of data. Therefore, we see performance benefits in terms of index size savings and read/write speed. Additionally, by grouping the data together with bucketing, we make it easier to organize specific groups of data, thus increasing the ability to discover historical trends or provide future forecasting. 

However, prior to MongoDB 5.0, in order to take advantage of bucketing, it required application code to be aware of bucketing and engineers to make conscious upfront schema decisions, which added overhead to developing efficient time series solutions within MongoDB. 

## Time Series Collections for Currency Analysis

Time Series collections are a new collection type introduced in MongoDB 5.0. It automatically optimizes for the storage of time series data and makes it easier, faster, and less expensive to work with time series data in MongoDB. There is a great [blog post](https://developer.mongodb.com/how-to/new-time-series-collections/) that covers MongoDB’s newly introduced Time Series collections in more detail that you may want to read first or for additional information.

For our use case, we will create a Time Series collection as follows:

```javascript
db.createCollection("ticker", {
 timeseries: {
   timeField: "time",
   metaField: "symbol",
 },
});

```

While defining the time series collection, we set the `timeField` of the time series collection as `time`, and the `metaField` of the time series collection as `symbol`. Therefore, a particular symbol’s data for a period will be stored together in the time series collection. 

### How the Currency Data is Stored in the Time Series Collection

The application code will make a simple insert operation as it does in a regular collection:

```javascript
db.ticker.insertOne({
 time: ISODate("20210101T01:00:00"),
 symbol: "BTC-USD",
 price: 34114.1145,
});
```

We read the data in the same way we would from any other MongoDB collection: 

```javascript
db.ticker.findOne({"symbol" : "BTC-USD"})

{
       "time": ISODate("20210101T01:00:00"),
       "symbol": "BTC-USD",
       "price": 34114.1145,
       "_id": ObjectId("611ea97417712c55f8d31651")
}
```

However, the underlying storage optimization specific to time series data will be done by MongoDB. For example, "BTC-USD" is a digital currency and every second you make an insert operation, it looks and feels like it’s stored as a separate document when you query it. However, the underlying optimization mechanism keeps the same symbols’ data together for faster and efficient processing. This allows us to automatically provide the advantages of the bucket pattern in terms of index size savings and read/write performance without sacrificing the way you work with your data.

## Candlestick Charts

We have already inserted hours of data for different currencies. A particular currency’s data is stored together, thanks to the Time Series collection. Now it’s time to start analyzing the currency data.

Now, instead of individually analyzing second level data, we will group the data by five-minute intervals, and then display the data on candlestick charts. Candlestick charts in technical analysis represent the movement in prices over a period of time. 

As an example, consider the following candlestick. It represents one time interval, e.g. five minutes between `20210101-17:30:00` and `20210101-17:35:00`, and it’s labeled with the start date, `20210101-17:30:00.` It has four metrics: high, low, open, and close. High is the highest price, low is the lowest price, open is the first price, and close is the last price of the currency in this duration.  

![Components of a candlestick chart](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/candlestick_chart_58903567ef.png)

In our currency dataset, we have to reach a stage where we need to have grouped the data by five-minute intervals like: `2021-01-01T01:00:00`, `2021-01-01T01:05:00`, etc. And every interval group needs to have four metrics: high, low, open, and close price. Examples of interval data are as follows:

```json
[{
   "time": ISODate("20210101T01:00:00"),
   "symbol": "BTC-USD",
   "open": 34111.12,
   "close": 34192.23,
   "high": 34513.28,
   "low": 33981.17
},
{
   "time": ISODate("20210101T01:05:00"),
   "symbol": "BTC-USD",
   "open": 34192.23,
   "close": 34244.16,
   "high": 34717.90,
   "low": 34001.13
}]
```

However, we only currently have second-level data for each ticker stored in our Time Series collection as we push the data for every second. We need to group the data, but how can we do this?

In addition to Time Series collections, MongoDB 5.0 has introduced a new aggregation operator, [`$dateTrunc`](https://docs.mongodb.com/manual/reference/operator/aggregation/dateTrunc/). This powerful new aggregation operator can do many things, but essentially, its core functionality is to truncate the date information to the closest time or a specific datepart, by considering the given parameters. In our scenario, we want to group currency data for five-minute intervals. Therefore, we can set the `$dateTrunc` operator parameters accordingly:

```json
{
   $dateTrunc: {
     date: "$time",
     unit: "minute",
     binSize: 5
   }
}
```

In order to set the high, low, open, and close prices for each group (each candlestick), we can use other MongoDB operators, which were already available before MongoDB 5.0:

- high: [`$max`](https://docs.mongodb.com/manual/reference/operator/aggregation/max/)
- low: [`$min`](https://docs.mongodb.com/manual/reference/operator/aggregation/min/)
- open: [`$first`](https://docs.mongodb.com/manual/reference/operator/aggregation/first/)
- close: [`$last`](https://docs.mongodb.com/manual/reference/operator/aggregation/last/)

After grouping the data, we need to sort the data by time to analyze it properly. Therefore, recent data (represented by a candlestick) will be at the right-most of the chart.

Putting this together, our entire aggregation query will look like this:

```js
db.ticker.aggregate([
 {
   $match: {
     symbol: "BTC-USD",
   },
 },
 {
   $group: {
     _id: {
       symbol: "$symbol",
       time: {
         $dateTrunc: {
           date: "$time",
           unit: "minute",
           binSize: 5
         },
       },
     },
     high: { $max: "$price" },
     low: { $min: "$price" },
     open: { $first: "$price" },
     close: { $last: "$price" },
   },
 },
 {
   $sort: {
     "_id.time": 1,
   },
 },
]);
```

After we grouped the data based on five-minute intervals, we can visualize it in a candlestick chart as follows:

![Candlestick chart](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/candlestick_chart2_c814ff7558.png)

We are currently using an [open source visualization tool](https://apexcharts.com/) to display five-minute grouped data of BTC-USD currency. Every stick in the chart represents a five-minute interval and has four metrics: high, low, open, and close price. 

## Conclusion

With the introduction of Time Series collections and advanced aggregation operators for date calculations, MongoDB 5.0 makes currency analysing much easier. 

After you’ve grouped the data for the selected intervals, you can allow MongoDB to remove old data by setting the [`expireAfterSeconds`](https://docs.mongodb.com/manual/core/timeseries-collections/) parameter in the collection options. It will automatically remove the older data than the specified time in seconds.

Another option is to archive raw data to cold storage for further analysis. Fortunately, [MongoDB Atlas has automatic archiving capability](https://www.mongodb.com/developer/how-to/manage-data-at-scale-with-online-archive/) to offload the old data in a MongoDB Atlas cluster to cold object storage, such as cloud object storage - Amazon S3 or Microsoft Azure Blob Storage. To do that, you can set your archiving rules on the time series collection and it will automatically offload the old data to the cold storage. Online Archive will be available for time-series collections very soon.

Is the currency data already placed in Kafka topics? That’s perfectly fine. You can easily transfer the data in Kafka topics to MongoDB through [MongoDB Sink Connector for Kafka](https://docs.mongodb.com/kafka-connector/current/kafka-sink/). Please check out this [article](https://www.mongodb.com/blog/post/streaming-time-series-data-using-apache-kafka-mongodb) for further details on the integration of Kafka topics and the MongoDB Time Series collection.

In the following posts, we’ll discuss how well-known financial technical indicators can be calculated via windowing functions on time series collections.

Time series collections part 1: generating data for a candlestick chart from time-series data

Currency Analysis with Time Series Collections #1 — Generating Candlestick Charts Data

In the [first post](https://www.mongodb.com/developer/article/time-series-candlestick/) of this series, we learned how to group currency data based on given time intervals to generate candlestick charts. In the [second article](https://www.mongodb.com/developer/article/time-series-candlestick-sma-ema), we learned how to calculate simple moving average and exponential moving average on the currencies based on a given time window. Now, in this post we’ll learn how to calculate more complex technical indicators.

## MACD Indicator

[MACD](https://www.investopedia.com/terms/m/macd.asp) (Moving Average Convergence Divergence) is another trading indicator and provides visibility of the trend and momentum of the currency/stock. MACD calculation fundamentally leverages multiple [EMA](https://www.investopedia.com/terms/e/ema.asp) calculations with different parameters.

As shown in the below diagram, MACD indicator has three main components: MACD Line, MACD Signal, and Histogram. (The blue line represents MACD Line, the red line represents MACD Signal, and green and red bars represent histogram):

![MACD histogram](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/MACD_Histogram_c2dbe7f43d.png)

- MACD Line is calculated by subtracting the 26-period (mostly, days are used for the period) exponential moving average from the 12-period exponential moving average. 
- After we get the MACD Line, we can calculate the MACD Signal. MACD Signal is calculated by getting the nine-period exponential moving average of MACD Line.
- MACD Histogram is calculated by subtracting the MACD Signal from the MACD Line. 

We can use the [MongoDB Aggregation Framework](https://docs.mongodb.com/manual/aggregation/) to calculate this complex indicator. 

In the previous blog posts, we learned how we can group the second-level raw data into five-minutes intervals through the `$group` stage and `$dateTrunc` operator:

```js
db.ticker.aggregate([
 {
   $match: {
     symbol: "BTC-USD",
   },
 },
 {
   $group: {
     _id: {
       symbol: "$symbol",
       time: {
         $dateTrunc: {
           date: "$time",
           unit: "minute",
           binSize: 5,
         },
       },
     },
     high: { $max: "$price" },
     low: { $min: "$price" },
     open: { $first: "$price" },
     close: { $last: "$price" },
   },
 },
 {
   $sort: {
     "_id.time": 1,
   },
 },
 {
   $project: {
     _id: 1,
     price: "$close",
   },
 }
]);
```

After that, we need to calculate two exponential moving averages with different parameters:

```js
{
   $setWindowFields: {
     partitionBy: "_id.symbol",
     sortBy: { "_id.time": 1 },
     output: {
       ema_12: {
         $expMovingAvg: { input: "$price", N: 12 },
       },
       ema_26: {
         $expMovingAvg: { input: "$price", N: 26 },
       },
     },
   },
}
```

After we calculate two separate exponential moving averages, we need to apply the `$subtract` operation in the next stage of the aggregation pipeline:

```js
{ $addFields : {"macdLine" : {"$subtract" : ["$ema_12", "$ema_26"]}}}
```

After we’ve obtained the `macdLine` field, then we can apply another exponential moving average to this newly generated field (`macdLine`) to obtain MACD signal value:

```js
{
   $setWindowFields: {
     partitionBy: "_id.symbol",
     sortBy: { "_id.time": 1 },
     output: {
       macdSignal: {
         $expMovingAvg: { input: "$macdLine", N: 9 },
       },
     },
   },
}
```

Therefore, we will have two more fields: `macdLine` and `macdSignal`. We can generate another field as `macdHistogram` that is calculated by subtracting the `macdSignal` from `macdLine` value:

```js
{ $addFields : {"macdHistogram" : {"$subtract" : ["$macdLine", "$macdSignal"]}}}
```

Now we have three derived fields: `macdLine`, `macdSignal`, and `macdHistogram`. Below, you can see how MACD is visualized together with Candlesticks:

![Candlestick charts](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/Candle_Stick_24919a4b2a.png)

This is the complete aggregation pipeline:

```js
db.ticker.aggregate([
 {
   $match: {
     symbol: "BTC-USD",
   },
 },
 {
   $group: {
     _id: {
       symbol: "$symbol",
       time: {
         $dateTrunc: {
           date: "$time",
           unit: "minute",
           binSize: 5,
         },
       },
     },
     high: { $max: "$price" },
     low: { $min: "$price" },
     open: { $first: "$price" },
     close: { $last: "$price" },
   },
 },
 {
   $sort: {
     "_id.time": 1,
   },
 },
 {
   $project: {
     _id: 1,
     price: "$close",
   },
 },
 {
   $setWindowFields: {
     partitionBy: "_id.symbol",
     sortBy: { "_id.time": 1 },
     output: {
       ema_12: {
         $expMovingAvg: { input: "$price", N: 12 },
       },
       ema_26: {
         $expMovingAvg: { input: "$price", N: 26 },
       },
     },
   },
 },
 { $addFields: { macdLine: { $subtract: ["$ema_12", "$ema_26"] } } },
 {
   $setWindowFields: {
     partitionBy: "_id.symbol",
     sortBy: { "_id.time": 1 },
     output: {
       macdSignal: {
         $expMovingAvg: { input: "$macdLine", N: 9 },
       },
     },
   },
 },
 {
   $addFields: { macdHistogram: { $subtract: ["$macdLine", "$macdSignal"] } },
 },
]);
```

## RSI Indicator

[RSI](https://www.investopedia.com/terms/r/rsi.asp) (Relativity Strength Index) is another financial technical indicator that reveals whether the asset has been overbought or oversold. It usually uses a 14-period time frame window, and the value of RSI is measured on a scale of 0 to 100. If the value is closer to 100, then it indicates that the asset has been overbought within this time period. And if the value is closer to 0, then it indicates that the asset has been oversold within this time period. Mostly, 70 and 30 are used for upper and lower thresholds.

Calculation of RSI is a bit more complicated than MACD:

- For every data point, the gain and the loss values are set by comparing one previous data point.
- After we set gain and loss values for every data point, then we can get a moving average of both gain and loss for a 14-period. (You don’t have to apply a 14-period. Whatever works for you, you can set accordingly.)
- After we get the average gain and the average loss value, we can divide average gain by average loss.
- After that, we can smooth the value to normalize it between 0 and 100.

### Calculating Gain and Loss

Firstly, we need to define the gain and the loss value for each interval. 

The gain and loss value are calculated by subtracting one previous price information from the current price information:

- If the difference is positive, it means there is a price increase and the value of the gain will be the difference between current price and previous price. The value of the loss will be 0.
- If the difference is negative, it means there is a price decline and the value of the loss will be the difference between previous price and current price. The value of the gain will be 0.

Consider the following input data set:

```js
{"_id": {"time": ISODate("20210101T17:00:00"), "symbol" : "BTC-USD"}, "price": 35050}
{"_id": {"time": ISODate("20210101T17:05:00"), "symbol" : "BTC-USD"}, "price": 35150}
{"_id": {"time": ISODate("20210101T17:10:00"), "symbol" : "BTC-USD"}, "price": 35280}
{"_id": {"time": ISODate("20210101T17:15:00"), "symbol" : "BTC-USD"}, "price": 34910}
```

Once we calculate the Gain and Loss, we will have the following data:

```js
{"_id": {"time": ISODate("20210101T17:00:00"), "symbol" : "BTC-USD"}, "price": 35050, "previousPrice": null, "gain":0, "loss":0}
{"_id": {"time": ISODate("20210101T17:05:00"), "symbol" : "BTC-USD"}, "price": 35150, "previousPrice": 35050, "gain":100, "loss":0}
{"_id": {"time": ISODate("20210101T17:10:00"), "symbol" : "BTC-USD"}, "price": 35280, "previousPrice": 35150, "gain":130, "loss":0}
{"_id": {"time": ISODate("20210101T17:15:00"), "symbol" : "BTC-USD"}, "price": 34910, "previousPrice": 35280, "gain":0, "loss":370}
```

But in the MongoDB Aggregation Pipeline, how can we refer to the previous document from the current document? How can we derive the new field (`$previousPrice`) from the previous document in the sorted window?  

MongoDB 5.0 introduced the [`$shift`](https://docs.mongodb.com/manual/reference/operator/aggregation/shift/#mongodb-group-grp.-shift) operator that includes data from another document in the same partition at the given location, e.g., you can refer to the document that is three documents before the current document or two documents after the current document in the sorted window.

We set our window with partitioning and introduce new field as previousPrice:

```js
{
   $setWindowFields: {
     partitionBy: "$_id.symbol",
     sortBy: { "_id.time": 1 },
     output: {
       previousPrice: { $shift: { by: -1, output: "$price" } },
     },
   },
}
```

`$shift` takes two parameters:

- `by` specifies the location of the document which we’ll include. Since we want to include the previous document, then we set it  to `-1`. If we wanted to include one next document, then we would set it to `1`.
- `output` specifies the field of the document that we want to include in the current document.

After we set the `$previousPrice` information for the current document, then we need to subtract the previous value from current value. We will have another derived field “`diff`” that represents the difference value between current value and previous value:

```js
{
   $addFields: {
     diff: {
       $subtract: ["$price", { $ifNull: ["$previousPrice", "$price"] }],
     },
   },
}
```

We’ve set the `diff` value and now we will set two more fields, `gain` and `loss,` to use in the further stages. We just apply the gain/loss logic here:

```js
{
   $addFields: {
     gain: { $cond: { if: { $gte: ["$diff", 0] }, then: "$diff", else: 0 } },
     loss: {
       $cond: { if: { $lte: ["$diff", 0] }, then: { $abs: "$diff" }, else: 0 },
     },
   },
}
```

After we have enriched the symbol data with gain and loss information for every document, then we can apply further partitioning to get the moving average of gain and loss fields by considering the previous 14 data points:

```js
{
   $setWindowFields: {
     partitionBy: "$_id.symbol",
     sortBy: { "_id.time": 1 },
     output: {
       avgGain: {
         $avg: "$gain",
         window: { documents: [-14, 0] },
       },
       avgLoss: {
         $avg: "$loss",
         window: { documents: [-14, 0] },
       },
       documentNumber: { $documentNumber: {} },
     },
   },
}
```

Here we also used another newly introduced operator, [`$documentNumber`](https://docs.mongodb.com/manual/reference/operator/aggregation/documentNumber/). While we do calculations over the window, we give a sequential number for each document, because we will filter out the documents that have the document number less than or equal to 14. (RSI is calculated after at least 14 data points have been arrived.) We will do filtering out in the later stages. Here, we only set the number of the document.

After we calculate the average gain and average loss for every symbol, then we will find the relative strength value. That is calculated by dividing average gain value by average loss value. Since we apply the divide operation, then we need to anticipate the “divide by 0” problem as well:

```js
{
   $addFields: {
     relativeStrength: {
       $cond: {
         if: {
           $gt: ["$avgLoss", 0],
         },
         then: {
           $divide: ["$avgGain", "$avgLoss"],
         },
         else: "$avgGain",
       },
     },
   },
}
```

Relative strength value has been calculated and now it’s time to smooth the Relative Strength value to normalize the data between 0 and 100:

```js
{
   $addFields: {
     rsi: {
       $cond: {
         if: { $gt: ["$documentNumber", 14] },
         then: {
           $subtract: [
             100,
             { $divide: [100, { $add: [1, "$relativeStrength"] }] },
           ],
         },
         else: null,
       },
     },
   },
}
```

We basically set `null` to the first 14 documents. And for the others, RSI value has been set.

Below, you can see a one-minute interval candlestick chart and RSI chart. After 14 data points, RSI starts to be calculated. For every interval, we calculated the RSI through aggregation queries by processing the previous data of that symbol:

![Candlestick charts](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/Candle_Stick2_dda548a743.png)

This is the complete aggregation pipeline:

```js
db.ticker.aggregate([
 {
   $match: {
     symbol: "BTC-USD",
   },
 },
 {
   $group: {
     _id: {
       symbol: "$symbol",
       time: {
         $dateTrunc: {
           date: "$time",
           unit: "minute",
           binSize: 5,
         },
       },
     },
     high: { $max: "$price" },
     low: { $min: "$price" },
     open: { $first: "$price" },
     close: { $last: "$price" },
   },
 },
 {
   $sort: {
     "_id.time": 1,
   },
 },
 {
   $project: {
     _id: 1,
     price: "$close",
   },
 },
 {
   $setWindowFields: {
     partitionBy: "$_id.symbol",
     sortBy: { "_id.time": 1 },
     output: {
       previousPrice: { $shift: { by: -1, output: "$price" } },
     },
   },
 },
 {
   $addFields: {
     diff: {
       $subtract: ["$price", { $ifNull: ["$previousPrice", "$price"] }],
     },
   },
 },
 {
   $addFields: {
     gain: { $cond: { if: { $gte: ["$diff", 0] }, then: "$diff", else: 0 } },
     loss: {
       $cond: { if: { $lte: ["$diff", 0] }, then: { $abs: "$diff" }, else: 0 },
     },
   },
 },
 {
   $setWindowFields: {
     partitionBy: "$_id.symbol",
     sortBy: { "_id.time": 1 },
     output: {
       avgGain: {
         $avg: "$gain",
         window: { documents: [-14, 0] },
       },
       avgLoss: {
         $avg: "$loss",
         window: { documents: [-14, 0] },
       },
       documentNumber: { $documentNumber: {} },
     },
   },
 },
 {
   $addFields: {
     relativeStrength: {
       $cond: {
         if: {
           $gt: ["$avgLoss", 0],
         },
         then: {
           $divide: ["$avgGain", "$avgLoss"],
         },
         else: "$avgGain",
       },
     },
   },
 },
 {
   $addFields: {
     rsi: {
       $cond: {
         if: { $gt: ["$documentNumber", 14] },
         then: {
           $subtract: [
             100,
             { $divide: [100, { $add: [1, "$relativeStrength"] }] },
           ],
         },
         else: null,
       },
     },
   },
 },
]);
```


## Conclusion

MongoDB Aggregation Framework provides a great toolset to transform any shape of data into a desired format. As you see in the examples, we use a wide variety of aggregation pipeline [stages](https://docs.mongodb.com/manual/reference/operator/aggregation-pipeline/) and [operators](https://docs.mongodb.com/manual/reference/operator/aggregation/). As we discussed in the previous blog posts, time-series collections and window functions are great tools to process time-based data over a window.

In this post we've looked at the $shift and $documentNumber operators that have been introduced with MongoDB 5.0. The `$shift` operator includes another document in the same window into the current document to process positional data together with current data. In an RSI technical indicator calculation, it is commonly used to compare the current data point with the previous data points, and `$shift` makes it easier to refer to positional documents in a window. For example, price difference between current data point and previous data point.

Another newly introduced operator is `$documentNumber`. `$documentNumber` gives a sequential number for the sorted documents to be processed later in subsequent aggregation stages. In an RSI calculation, we need to skip calculating RSI value for the first 14 periods of data and $documentNumber helps us to identify and filter out these documents at later stages in the aggregation pipeline. 

Time series collections part 3: calculating MACD & RSI values

Currency Analysis with Time Series Collections #3 — MACD and RSI Calculation

Atlas Data API provides a serverless API layer on top of your data in Atlas. You can natively configure rule-based access for a set of users with different permissions.

Fuat Sungur

Content by Fuat Sungur

Visualize MongoDB Atlas Database Audit Logs

Currency Analysis with Time Series Collections #2 — Simple Moving Average and Exponential Moving Average Calculation

Currency Analysis with Time Series Collections #1 — Generating Candlestick Charts Data

Currency Analysis with Time Series Collections #3 — MACD and RSI Calculation

Rule-Based Access to Atlas Data API