I am after an mysql query that shows the correlation between 10 years of forex data.
I have attached a file with 3 currencies which can be loaded into mysql?
with the following command:
mysql> load data local infile '/home/ruben/scripts/[url removed, login to view]' into table eurusd fields terminated by ',' lines terminated by '\n' (date,time,open,high,low,close,volume);
Now what I am after is a table that looks the following
If we do (open -close) for a given time period range of days/week/ or month
we know that for that period it would be ? "up" or "down" for that period depending if (open-close) >0 or < 0?
on the header of the table
I would like to see
Currency|date range|2000|2001|2002|2003|2004|2005|2006|2007|2008|2009 etc dynamically generated from the dataset.|total of the deltas for each year| %success rate up(count of all delta positive/years*100) |%success rate down(count of all delta negative/years *100)|?
For example outcome:
Currency|date range|1999 ? |2000|2002|2003|2004|2005|2006|2007|2008|2009|% up|% down|total deltas
Eurusd |for example weekly ? |0.0254|0.0343|[url removed, login to view]|0.0323|0.0233|0.0232|etc| 80%|20%|2033|
and then 52 rows as a year has 52 weeks
now I also want to be able to sort on the last 3 columns?
I hope everything is clear, however if you have questions please do not hesitate to ask me