public
Created

Here is a brief pl/pgsql code to calculate the MACD indicator from a table storing daily candles. It's not finished and help is welcome, either wrt efficiency or wrt correctness, for some reason EMA formulas online are calculated with alpha [0,1] whereas

  • Download Gist
macd_aggregator.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
create table candles(ticker text, day date, value real, primary key (ticker, day));
 
drop table macdtuple cascade;
CREATE TABLE macdtuple (ticker real, ema1val real, ema2val real, signalval real);
 
create or replace function macd_tuple_func(state macdtuple, inval real, ticker real, alpha1 real, alpha2 real, alpha3 real) returns macdtuple as $previousmacd$
declare
x macdtuple;
begin
if (state is null OR ticker <> ticker(state)) then
x.ticker := ticker;
x.ema1val := inval;
x.ema2val := inval;
x.signalval := 0;
return x;
else
x.ticker := ticker;
x.ema1val := alpha1 * inval + (1 - alpha1) * ema1val(state);
x.ema2val := alpha2 * inval + (1 - alpha2) * ema2val(state);
x.signalval := alpha3 * (x.ema1val-x.ema2val) + (1 - alpha3) * signalval(state);
return x;
end if;
end
$previousmacd$ language plpgsql;
 
 
create or replace function macd_histogram_final_func(state macdtuple) returns real as $transmacdh$
begin
return ema1val(state)-ema2val(state)-signalval(state);
end
$transmacdh$ language plpgsql;
 
create aggregate macd_tuple(real, real, real, real, real) (sfunc = macd_tuple_func, stype = macdtuple, finalfunc=macd_histogram_final_func);
 
select id_empresa, cierre, macd_tuple(cierre, id_empresa, 0.1, 0.2, 0.05) over (order by id_empresa, tiempo), tiempo from vela;
 
select ticker, value, macd_tuple(value, ticker, 0.1, 0.2, 0.05) over (order by ticker, day), day from candles;

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.