Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
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
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$
x macdtuple;
if (state is null OR ticker <> ticker(state)) then
x.ticker := ticker;
x.ema1val := inval;
x.ema2val := inval;
x.signalval := 0;
return x;
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;
$previousmacd$ language plpgsql;
create or replace function macd_histogram_final_func(state macdtuple) returns real as $transmacdh$
return ema1val(state)-ema2val(state)-signalval(state);
$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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment