FOREX_LAST_27_DAYS_TREND function
based on the following www links
http://onlinestatbook.com/2/regression/intro.html
http://onlinestatbook.com/2/describing_ ... ation.html
the tables concerned are
FOREX contains Foreign Exchange rates (manually) input daily by eyeball and fingers from the site
http://www.xe.com/currencycharts/
FOREX_LAST_27_DAYS contains the most recent 28 days from FOREX "normalised" relative to the first entry in each column as 1
I used that to produce a nice graph with all the rates emanating and criss-crossing from a common origin
But what I needed more was a simple indication of whether or not the trend is falling
because if it is falling and a transfer is due then it ought to happen sooner
whereas otherwise I can wait a bit, it might go up a bit more
(FX LOL)
There are two rates I need to know, GBPUSD (Great British Pounds to US Dollars) and USDPEN (USD to Peruvian Nuevos Soles)
It could be more, it could be less (I might want to incorporate Euros later, for example)
The returned data is "b" for the slope of the trend line and "last" for the last known value
But for the purpose of finding the generalised trend (Down, Level, Up) only "b" is useful
I have ignored the Intercept ("A") here but it could be incorporated quite simply if required
I have used Sample stddev rather than Population because FX is a bit rubbish anyway as far as stats are concerned
Finally there are a few redundant variables in here, I have provided the code "as is" rather than editing it down for ease of consumption
The X Y and other stuff relates directly to the nice book as linked above
Good Wishes
David
Code: Select all
create function FOREX_LAST_27_DAYS_TREND()
returns table
(
"b(GBPUSD)" decimal(15,9),
"b(USDPEN)" decimal(15,9),
"last(GBPUSD)" decimal(10,8),
"last(USDPEN)" decimal(10,8)
)
specific FOREX_LAST_27_DAYS_TREND
reads SQL data
begin atomic
return table
(
select
cast(GBP_USD_X_Y_OR_TREND_GROSS * STDDEV_GBPUSD / STDDEV_LOGGED as decimal(15,9)) as "b(GBPUSD)",
cast(USD_PEN_X_Y_OR_TREND_GROSS * STDDEV_USDPEN / STDDEV_LOGGED as decimal(15,9)) as "b(USDPEN)",
(select GBPUSD_XE from FOREX where DATE_LOGGED = (select max(DATE_LOGGED) from FOREX)) as "last(GBPUSD)",
(select USDPEN_XE from FOREX where DATE_LOGGED = (select max(DATE_LOGGED) from FOREX)) as "last(USDPEN)"
from
(
select
(select stddev_samp(GBP_USD) from "View FOREX_LAST_27_DAYS") as STDDEV_GBPUSD,
(select stddev_samp(USD_PEN) from "View FOREX_LAST_27_DAYS") as STDDEV_USDPEN,
(select stddev_samp(datediff(DATE_LOGGED,date '1899-12-30')) from "View FOREX_LAST_27_DAYS") as STDDEV_LOGGED,
GBP_USD_X_Y_OR_TREND_GROSS,
USD_PEN_X_Y_OR_TREND_GROSS
from
(
select
sum("xy GBP_USD") as GBP_USD_X_Y_OR_TREND_GROSS,
sum("xy USD_PEN") as USD_PEN_X_Y_OR_TREND_GROSS
from
(
select
power("normalised DATE_LOGGED",2) as "x**2 DATE_LOGGED"
,
power("normalised GBP_USD",2) as "y**2 GBP_USD"
,
power("normalised USD_PEN",2) as "y**2 USD_PEN"
,
"normalised GBP_USD"*"normalised DATE_LOGGED" as "xy GBP_USD"
,
"normalised USD_PEN"*"normalised DATE_LOGGED" as "xy USD_PEN"
from
(
select
(
(
unix_timestamp(DATE_LOGGED)
-
(
select
avg(unix_timestamp(DATE_LOGGED))
from
"View FOREX_LAST_27_DAYS"
)
)
/
(
select
stddev_samp(unix_timestamp(DATE_LOGGED))
from
"View FOREX_LAST_27_DAYS"
)
) as "normalised DATE_LOGGED"
,
(
(
GBP_USD
-
(
select
avg(GBP_USD)
from
"View FOREX_LAST_27_DAYS"
)
)
/
(
select
stddev_samp(GBP_USD)
from
"View FOREX_LAST_27_DAYS"
)
) as "normalised GBP_USD"
,
(
(
USD_PEN
-
(
select
avg(USD_PEN)
from
"View FOREX_LAST_27_DAYS"
)
)
/
(
select
stddev_samp(USD_PEN)
from
"View FOREX_LAST_27_DAYS"
)
) as "normalised USD_PEN"
from
"View FOREX_LAST_27_DAYS"
)
)
)
)
);
end;