I have gathered some historical price data of currency pairs (forex, fx, foreign exchange market). They are in CSV format now as follows:
and so on...
The values refer to Japanese candlestick charts as follows:
[login to view URL],BARopenTIME,BARopenPRICE,BARhighestPRICE,BARlowestPRICE,BARclosePRICE,Volume
Said price data include M15 data for EURUSD, USDJPY, GBPUSD, USDCHF, USDCAD, AUDUSD and NZDUSD (“M15 data” means that 1 bar/candle summarizes 15 minutes of price volatility). Download:
[login to view URL]
The data of each symbol covers about 15 years of historical prices, starting in May/August 2003 (first cell/column on top) and closing in October 2018 (last column/cell in the list). Now I want to “merge” all price data to build an artificial history over about 100 years by adding all price data as follows:
1. Step: USDJPY copied/shifted to/above EURUSD => CSV file 1
2. Step: GBPUSD copied/shifted to/above “1.” => CSV file 2
3. Step: USDCHF copied/shifted to/above “2.” => CSV file 3
So the 6th and last step will result in “CSV file 6” containing:
NZDUSD (starting about 100 years ago)
EURUSD (closing in October 2018)
However there are some difficulties:
A) Each current file contains about 400k columns and because of Excel’s limitation to about 1 million columns said program can’t be used for merging all data (maybe MySQL etc. can be suitable).
B) USDJPY prices are in the format [login to view URL] (e.g. 112.508) whereas all other currencies are formatted as [login to view URL] (e.g. 1.14483). Therefore ALL USDJPY prices must initially be divided by 100.
C) When reaching the transition e.g. USDJPY->EURUSD, USDJPY must be shifted to seamlessly (without any price gap) link to EURUSD’s first price. EURUSD’s first column/cell shows:
The first price “1.12284” is the bar’s/candle’s open price. So in the next step we must have a look at USDJPY’s last price according to the respective CSV file’s raw data:
The last price is the respective bar’s/candle’s close price “112.353”. After being divided by 100 (see above) we get 1.12353. The difference to EURUSD’s first price is 1.12284 - 1.12353 = -0.00069. Therefore ALL prices of USDJPY have to be shifted by -0.00069 [for most currency pairs the required amount of shifting will be much bigger].
D) There must not be any bigger (time) breaks between two “merged” currency pairs’ price data. So if e.g. EURUSD as well as USDJPY data start on “2003.05.05,00:00” and finish on “2018.10.29,23:45” (the “M15 bar/candle” includes the respective price’s whole volatility till midnight then), the EURUSD price candles’ times must first ALL be shifted 385606 columns/cells (= number of columns of USDJPY) into the past. At the same time all said EURUSD candles’ times must be shifted by 5670 days into the past (including some “buffer” for leapyear). When adding USDCHF and AUDUSD all previously already “merged” price data must only be shifted by 5575 days because said two currency pairs’ data start a little bit later on “2003.08.04,00:00”. It doesn’t matter that after time shifting the price candles a “Monday candle” may become a “Sunday candle” etc. Furthermore it also doesn’t matter that there may be some (consecutive) days without any data.
I hope that everything is explained comprehensible.
Who can do this job accurately? Please make an offer including cost and processing time, thanks.
I have Carried out statistical analysis on the rise of education and GDP growth. I have done SQL data analysis that i used in sorting out different form of insight
13 фрілансерів(-и) у середньому готові виконати цю роботу за €142
Please review my profile as i have relevant skills and experience required for this project. Kindly send me a message to discuss further. Thanks, Asad Khan
I am a professional accountant with a decade of experience working in a US based NASDAQ registered company's finance department with excellent excel skills and knowledge.
Hi, I've got quite some experience with C# and automation tasks such as this one and I'm sure I can help you out here. May I get some more details over the chat?