How can I sum depending on sign in sql? Financial technical indicators

Question

I'm trying to build some custom functions of technical indicators in postgresql and a couple of doubts came into my mind. The first one is if it is any library or plugin that already have this indicators (the common ones like moving averages, RSI, MACD, RSI, MFI, etc).

My second question is about an indicator that I'm trying to code. The MFI (Money Flow Index) indicator is defined as follows:

• Calculate the typical price: (high + low + close) / 3
• Calculate the raw money flow: typical price x volume
• Calculate the money flow ratio: (14-period positive money flow) / (14-period negative money flow) (**)
• Calculate the MFI: 100 – 100 / (1 + money flow ratio)

What I have already done is this:

with actual_close as (
    select 
    date, 
    (high+low+close)/3*1000 v_ty_price,  --This is the typical price x volume, since in this case is always 1000
    close actual_close
    from eurusd_ohlc
), 
prev_close as(
                select date,
                      v_ty_price,
                      actual_close,
                      lag(actual_close,1) over(order by date) prev_close 
                      from actual_close), 
totals as (
    select date,
           v_ty_price, 
           actual_close, 
           prev_close, 
           actual_close-prev_close close_dif 
           from prev_close
)

select date, sum(close_dif) over(rows between 14 preceding and current row) from totals 

Which yields the following output:

-------------------------------------------------------
|          date             |         sum             |
|2020-02-20 03:27:35.140751 |         NULL            |
|2020-02-20 04:19:17.088462 |   -6.000000000017103e-05|
|2020-02-20 05:54:44.060929 |           0             |
|2020-02-20 06:41:32.916934 |   -0.0006200000000000649|

This output is what should be used in (**). That part would be:

• Grab last 14 rows
• Add all positive numbers together and all negative numbers together
• Divide the sum of all positive by all negative.

So, lets assume the last 14 rows are like follows:

sum
-0.1
-0.6
1.2
0.1
1.5
-1
1
-0.2
0
-1
1
1.5
1.1
-1.1

So the each sum would be:

pos_sum = 1.2 + 0.1 + 1.5 + 1 + 0 + 1 + 1.5 + 1.1

neg_sum = 0.1 + 0.6 + 1 + 0.2 + 1 + 1.1

Then the Money Flow ratio is calculated as pos_sum/neg_sum (OBS: MFI is a bounded indicator between 0 and 100, if neg_sum = 0 then the Money Flow Ratio is 100).

Then, finally MFI is calculated by doing 100 – 100 / (1 + money flow ratio).

The part where I'm having troubles is in calculating the pos_sum and the neg_sum. How can I calculate those terms and do it in a rolling way? (or moving window)

Thank you in advance! Any suggestion is appreciated :)

Answer 1

If you have already your 14 rows selected as a table you can get your sum of positive, sum of negatives as

I renamed your sum column to sum_col

WITH SUM_TABLE AS (
SELECT
SUM(CASE WHEN sum_col>=0 THEN sum_col ELSE 0 END) AS POS_SUM,
SUM(CASE WHEN sum_col<0 THEN sum_col ELSE 0 END) AS NEG_SUM
FROM table)
SELECT
CASE WHEN NEG_SUM=0 THEN 100 ELSE POS_SUM/NEG_SUM END AS MFR,
CASE WHEN NEG_SUM=0 THEN 100-100/(1+100) ELSE 100-100/(1 + (POS_SUM/NEG_SUM)) END AS MFI
FROM SUM_TABLE

EDIT Since you are using PostreSQL, if your table has more than 14 rows and need to do a moving calculation, you can use OVER function to do like a group by for each of them.

WITH SUM_TABLE AS (
SELECT
date,
SUM(CASE WHEN sum_col>=0 THEN sum_col ELSE 0 END) OVER(ORDER BY date ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS POS_SUM,
SUM(CASE WHEN sum_col<0 THEN sum_col ELSE 0 END) OVER(ORDER BY date ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS NEG_SUM
FROM table)
SELECT
date,
CASE WHEN NEG_SUM=0 THEN 100 ELSE POS_SUM/NEG_SUM END AS MFR,
CASE WHEN NEG_SUM=0 THEN 100-100/(1+100) ELSE 100-100/(1 + (POS_SUM/NEG_SUM)) END AS MFI
FROM SUM_TABLE