# 4-1. Simple Moving Average (SMA)
In this session, we are using a simple example to demonstrate how to configure an API route as an endpoint by connecting to a basic "Simple Moving Average (SMA)" data process, with the data source from MySQL to compute the SMA for a specific time period from today. That is, when a user triggers this data process with the input of *Stock No.* and a *Window*, it will read the data of that specific stock, calculate MA corresponding to the required window, and lastly return an MA value for that stock.
In this example, in addition to logic writing, we focus more on the **design thinking of the entire process** and the **use of agents**.
First of all, we need to specify our requirements. In this example, we want to provide additional information into the data process on top of the existing data. Based on this, we begin to imagine where/what/how users can get this information through the API. To recap, we have mentioned these design ideas of "building a B/D process" in the previous topic, and we are mapping these design requirements back to that:
- What **Trigger** point does the user use to execute a data process?
- Which **Data Source** should the user read/write?
- Finally, what kind of **Output** does user want?
These elements are what functionalities we should build for an API.
To execute this data process, we are going to:
(I) initiate a new data process template file
(II) design an empty data process with an API route to ensure this API endpoint can be connected
(III) connect with an HTTP agent and a DB agent to retrieve data
(IV) complete SMA Data Process
(V) deploy and execute the completed SMA data process.
---
## (I) Initiate New Data Process Template File
At the beginning, let us start the whole journey by
```
loc new [template name]
```
:::info
If you could, please use different template name. We suggest the template can be `username_getma`. For instance, you might use this command to create your own data process template file:
`loc new john_getma`
:::
You will then have a file containing these documents under your specified directory.
---
## (II) Design Data Process with API Route
Before anything, we firstly would like to ensure the endpoint of this data process. Thus, in this step, we are configuring an API route with an empty data process to see if the path to trigger this data process can run successfully.
#### Try it
Here is the complete setup of the API route in the file of *"api-route-config.yaml"*.
```yaml=
method: POST
mode: Sync
encapsulation: true
name: get-ma-cue_staging
path: /john/getMA/cueStaging
dataProcessPids:
- pid: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
revision: latest
```
:::info
If you could, please use different path name. We suggest the path name can be ` /[username]/getma/cueStaging`. For instance, `/john/getMA/cueStaging`.
:::
To summarise, we have changed
(1) the method from *GET* to **POST**
(2) the name from *this-is-an-example* to **get-ma-cue_staging**, and
(3) the path from *`/this/is/an/example/path`* to **`/[username]/getMA/cueStaging`**.
Please note that since we are trying to check whether this endpoint is successfully connected, we leave generic logics and the aggregator logic unchanged/empty. Still, please don't forget to switch to `config.yaml` to change the name of these logics.
Once the setup has been done, we are using the command of
`loc deploy [template name] -ar`
to deploy this empty data process and configure an API route.
:::info
Because of ```-ar``` command,
it will connect dp to ar automatically without manually setting ```pid``` whilst deploying.
:::
To trigger this API route, you might need Postman to help. Once putting the complete path of this empty data process, it will return the status of 200, meaning that we have successfully designed an API. Next, we will start to expand this API, to complete the logics and data process, and eventually to make it successfully respond the SMA data we request.
---
## (III-1) Design Generic Logic - Parse Input from Payload
In this generic logic, we perform a series of operations to parse the JSON body, to transform the payload into string (if it is an http payload), and lastly to check the payload string format and put each of the value into a constant variable, including the stock code and the specified window to calculate the simple moving average.
#### Try it
Here is the generic logic you can put into your 1.js. (As for 2.js, you can leave it untouched for now.)
:::spoiler **<font color=Indigo>Generic Logic 1 - Import Payload</font>**
++**1.js**++
```javascript=
/**
*
* The codes in 'run' is executed when no error occurred in Generic Logic.
*
*/
// PARSING PAYLOAD FROM API BODY
export async function run(ctx) {
// checking input from api
if ("event" in ctx.payload || "messageQueue" in ctx.payload) {
throw new Error("this logic accept http payload only");
}
// a function that transforms byte array to string
function UTF8ArrToStr(aBytes) {
let utf8decoder = new TextDecoder();
return utf8decoder.decode(new Uint8Array(aBytes));
}
const getHttpBodyJson = JSON.parse(UTF8ArrToStr(ctx.payload.http.body));
// parse payload body
const data_payload = getHttpBodyJson;
// checking the payload is as defined
const days = parseInt(data_payload.days ?? "0");
const stock_code = data_payload.stock_code ?? "";
if (days !== 20 && days !== 50 && days !== 100) {
throw "days should be 20 | 50 | 100";
}
// store "days" and "stock_code" in the "data_payload" session storage
await ctx.agents.sessionStorage.putJson("result", {
days: days,
stock_code: stock_code,
});
}
/**
*
* The codes in 'handleError' is executed when there is any error occurred
* in Generic Logic or CURRENT running Logic just gets an error.
*
*/
export async function handleError(ctx, error) {
ctx.agents.logging.error(error.message);
}
```
:::
\
And the aggregator logic can be like this. **To simplify, we are going to use these code snippets of the aggregator logic throughout our SMA example.**
:::spoiler **<font color=Indigo>Aggregator Logic</font>**
++**aggregator-logic.js**++
```javascript=
/**
*
* The codes in 'run' is executed when no error occurred in Aggregator Logic.
*
*/
export async function run(ctx) {
let result = {
Status: 200,
Message: 'Calling API success.',
API_calling_details: await ctx.agents.sessionStorage.get("result"),
};
ctx.agents.result.finalize(result);
}
/**
*
* The codes in 'handleError' is executed when there is any error occurred
* in Aggregator Logic or CURRENT running Logic just gets an error.
*
*/
export async function handleError(ctx, error) {
ctx.agents.logging.error(error.message);
let result = {
status: 500,
errorMessage: `An error occurs when calling API. Error: ${error.message}`,
};
ctx.agents.result.finalize(result);
}
```
:::
After completing the generic logic and the aggregator logic, please don't forget to switch to `config.yaml` and `api-route-config.yaml`, both of which you can use exactly the same ones as [previously](https://hackmd.io/mOt9OudrTee1ymm5oKZxCw?view#Try-it). Once the setup has been done, again, we are using the command of
`loc deploy [template name] -ar`
to deploy this data process and configure the API route.
++**api-route-config.yaml**++
```yaml=
method: POST
mode: Sync
encapsulation: true
name: get-ma-cue_staging
path: /getMA/cueStaging
dataProcessPids:
- pid: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
revision: latest
```
As for the request body in Postman, there must be 2 parameters, "days" and "stock_code" for the purpose of fetching these data into the data process and start triggering it.
Please note that the value of "days" (which is our so-called windows aforementioned) can be 20, 50 or 100; the value of "stock_code" can be those captured in the DB.
The example of the payload can be like this.
++**payload**++
```json
{
"days":50,
"stock_code":"1330"
}
```
Now, let's test how parsing turns out to be by using Postman to trigger this data process:
#### <span style="color:green">++**[if OK]**++</span>
#### <span style="color:red">++**[if Error]**++</span>
Finally, we successfully parsed the payload into the data process and return the desired result.
Nevertheless, if the user's input is incorrect, there will be an error response just as we have designed in the aggregator logic. This means that we actually handle the payload and apply it to the data process correctly.
---
## (III-2) Design Generic Logic - Use DB Agent
After parsing the input from users, we also need to read the corresponding stock data from the DB to this data process. Therefore, we will use "Database Agent" here to access the DB.
In this example, we put all the required information into the "connection" variable, and connect with MySQL through the agent to query the data needed to calculate the MA.
#### Try it
Please note that the database connection information is actually obtained from `profile.yaml`. In other words, you can use the command of
`loc profile get` to acquire the DB connection information.
Usually, we can directly write the DB connection information here. But in this example, for security issues, we use ```process.env``` to access the DB information previously set in profile.yaml, so that the information does not appear in the code.
++**1.js**++
```javascript=
/**
*
* The codes in 'run' is executed when no error occurred in Generic Logic.
*
*/
// MYSQL DATABASE AGENT
export async function run(ctx) {
// mysql authorized access
const database = process.env.db1_database;
const table = process.env.db1_table4;
let connection = new Saffron.Database.MySqlParameters({
host: process.env.db1_host,
port: parseInt(process.env.db1_port ?? "3306"),
database,
username: process.env.db1_username,
password: process.env.db1_password,
});
let db = await ctx.agents.database?.connect({
databaseDriver: Saffron.Database.Driver.MySql,
connection,
});
await ctx.agents.sessionStorage.putJson("result", db);
await db?.disconnect()
}
/**
*
* The codes in 'handleError' is executed when there is any error occurred
* in Generic Logic or CURRENT running Logic just gets an error.
*
*/
export async function handleError(ctx, error) {
ctx.agents.logging.error(error.message);
}
```
++**aggregator-logic.js**++
It is the same as the [previous one](https://hackmd.io/moW5iXpIREGEpb-eA62uYQ?view#Try-it1).
:::info
To know more about LOC agents and their usage, please refer [**Agent List**](https://hackmd.io/Uj-tC7l9Q82VyGr8R5PL2Q?view#Database).
:::
Once the setup has been done, again, we are using the command of
`loc deploy [template name] -ar`
to deploy this data process and configure the API route.
In order to explain each step in detail, we are now checking whether the connection is successful by using Postman:
#### <span style="color:green">++**[if OK]**++</span>
As you can see, the API responds with the "uid" connection information, meaning that we have successfully connected to the specified MySQL database through the logic settings. Now we can access the required stock data to calculate MA by querying the database!
---
## (IV) Complete SMA Data Process
So, basically we have prepared most of the stuff for this SMA data process. In this step, we are going to combine the previous steps altogether and come out a complete SMA data process.
However, before we do so, there is one more thing we would like to talk about separately - how to calculate MA. Because the data we extract from the DB is the raw data, we need to do some calculations to create SMA. Therefore, we choose to calculate SMA directly through SQL. Of course, you can also use JavaScript to do the same trick in the generic logic.
++**example of calculating MA via SQL**++
```javascript=
// sql query and average through agent
let sql = `SELECT avg(close) as MA, stock_code from ${table} \
WHERE date >= date_sub(now(), interval ${data_payload["days"]} day)`;
if (data_payload["stock_code"] !== "")
sql += ` AND stock_code = '${data_payload["stock_code"]}'`;
sql += " GROUP BY stock_code limit 100;";
ctx.agents.logging.info(sql);
let resp = await db?.query(sql, []);
// build result
let data = [];
let ma = "";
resp?.rows.forEach((row) => {
ma = row["MA"];
if (ma !== "") {
data.push({ stock_code: row["stock_code"], MA: ma });
}
});
```
#### Try it
When we put everything together including calculating SMA via SQL, we will have this complete generic logic of `1.js`. With this complete generic logic and the aggregator logic, a data process for SMA is finally built!
++**1.js**++
```javascript=
/**
*
* The codes in 'run' is executed when no error occurred in Generic Logic.
*
*/
export async function run(ctx) {
// checking input from api
if ("event" in ctx.payload || "messageQueue" in ctx.payload) {
throw new Error("this logic accept http payload only");
}
// a function that transforms byte array to string
function UTF8ArrToStr(aBytes) {
let utf8decoder = new TextDecoder();
return utf8decoder.decode(new Uint8Array(aBytes));
}
const getHttpBodyJson = JSON.parse(UTF8ArrToStr(ctx.payload.http.body));
// parse payload body
const data_payload = getHttpBodyJson;
// checking the payload is as defined
const days = parseInt(data_payload.days ?? "0");
const stock_code = data_payload.stock_code ?? "";
if (days !== 20 && days !== 50 && days !== 100) {
throw "days should be 20 | 50 | 100";
}
// mysql authorized access
const database = process.env.db1_database;
const table = process.env.db1_table4;
let connection = new Saffron.Database.MySqlParameters({
host: process.env.db1_host,
port: parseInt(process.env.db1_port ?? "3306"),
database,
username: process.env.db1_username,
password: process.env.db1_password,
});
let db = await ctx.agents.database?.connect({
databaseDriver: Saffron.Database.Driver.MySql,
connection,
});
// sql query and average
let sql = `SELECT avg(close) as MA, stock_code from ${table} \
WHERE date >= date_sub(now(), interval ${data_payload["days"]} day)`;
if (data_payload["stock_code"] !== "")
sql += ` AND stock_code = '${data_payload["stock_code"]}'`;
sql += " GROUP BY stock_code limit 100;";
// ctx.agents.logging.info(sql);
let resp = await db?.query(sql, []);
// put result
let data = [];
let ma = "";
resp?.rows.forEach((row) => {
ma = row["MA"];
if (ma !== "") {
data.push({ stock_code: row["stock_code"], MA: ma });
}
});
await ctx.agents.sessionStorage.putJson("result", data)
}
/**
*
* The codes in 'handleError' is executed when there is any error occurred
* in Generic Logic or CURRENT running Logic just gets an error.
*
*/
export async function handleError(ctx, error) {
ctx.agents.logging.error(error.message);
}
```
++**aggregator-logic.js**++
It is the same as the [previous one](https://hackmd.io/moW5iXpIREGEpb-eA62uYQ?view#Try-it1).
---
## (V) Deploy & Execute Data Process
After completing the entire data process (generic logic + aggregator logic), now we can finally mount the data process on the API so that users can get the SMA result!
We can use the same command to deploy this data process and configure the API route:
`loc deploy [template name] -ar`
### Prepare Payload
Now we can simulate how the user gets the result by calling the API, In this example, we are going to specify the SMA result we want to get through the http request body.
Hence, the payload we use below must contain 2 parameters, "days" and "stock_code" for the purpose of feeding these data into the data process and starting to trigger it.
Note that the value of "days" (which is our so-called windows aforementioned) can be 20, 50 or 100; the value of "stock_code" can be those captured in the DB.
The example of the payload can be like this.
++**payload**++
```json
{
"days":50,
"stock_code":"1330"
}
```
### Response of Triggering API Route
You can use an API client tool such as **[Postman](https://www.postman.com/)** or **[Insomnia](https://insomnia.rest/)** to request an URL. Here we are using Postman for example.
Select POST and paste the full API route (including server URL). Afterwards, paste the JSON payload you just made.
:::info
For more about Postman and Insomnia:
- ++[Basics of API Testing Using Postman](https://www.geeksforgeeks.org/basics-of-api-testing-using-postman/)++
- ++[Understanding Insomnia REST Client Made Easy 101](https://hevodata.com/learn/insomnia-rest-client/)++
:::
In this example, we have demonstrated how to quickly build a data product based on the scenario requirements through LOC, including how to call the agent to access various data, and how to design the API route and the related data process. Based on this model, architects can focus on functional design, while developers can focus on logic development so as to meet every function requirement. If an adjustment is needed, this service can be taken offline by modifying logics and re-launched online immediately. Therefore, LOC helps to achieve a lighter-weight and more specialised function-as-a service process.
---
## Reference:
### What is MA?
In statistics, a moving average (MA) is a calculation used to analyse data points by creating a series of averages of different subsets of the full data set that captures the average change in a data series over time.
In finance, a moving average (MA) is a stock indicator that is commonly used in technical analysis, to help smooth out the price data by creating a constantly updated average price, often used by technical analysts to keep track of prices trends for specific securities.
The simplest form of a moving average, known as a simple moving average (SMA), is calculated by **taking the arithmetic mean of a given set of values over a specified period of time**. The formula for calculating the simple moving average is as below:
::: success
**Simple Moving Average**
$$
SMA_n = {A_1 + A_2 + A_3... + A_n \over n}.
$$
:::
It means that SMA contains two main parameters, namely the "summation" (A), the total values over period *n*; and the "window", the number of time periods.
:::warning
Source: [Moving Average](https://corporatefinanceinstitute.com/resources/knowledge/other/moving-average/)
:::
---
###### tags: `Workshop`
Sign in with Wallet
Connect another wallet