Hi

The above comes down as such:- 01.05.2003 08:00:00.000 in one field
I view the data in ms Excel
I wish to split the single field into two fields - Date and Time
and use the resulting fields to get day-of-week etc.

My problem is that I can't seem to get a date field and a time field from the original single Dukascopy field

Any suggestions please ?

Bob M

Hi Bob,

In my system, that I recommended to you in the other post:

https://www.dukascopy.com/fxcomm/fx-article-contest/?Jforex-Excel&action=read&id=1468

You have:

@Configurable("Date Format")
    public String  dformat = "dd/MM/yyyy HH:mm:ss";
    DateFormat df = new SimpleDateFormat(dformat);

If you want the Date & Time separately, you can split as following:

@Configurable("Date Format")
    public String  dformat = "dd/MM/yyyy";
@Configurable("Time Format")
    public String  tformat = "HH:mm:ss";
    DateFormat df = new SimpleDateFormat(dformat);
    DateFormat tf = new SimpleDateFormat(tformat);

you will need change these 2 lines too:

this.OlExcel=new OLExcel(limit+1, 7); //1 column more (Bars)

this.OlExcel=new OLExcel(limit+1, 6); //1 column more (Ticks)

and then, you can add a title, the titles line for Bars would look like:

OLExcel.addRow(OLExcel.setCell("Date",S), OLExcel.setCell("Time",S), OLExcel.setCell("Open",S), OLExcel.setCell("High",S), OLExcel.setCell("Low",S), 
                        OLExcel.setCell("Close",S) , OLExcel.setCell("Volume",S));

and for Ticks:

OLExcel.addRow(OLExcel.setCell("Date",S), OLExcel.setCell("Time",S), OLExcel.setCell("Ask",S), OLExcel.setCell("Ask Volume",S), OLExcel.setCell("Bid",S), 
                        OLExcel.setCell("Bid Volume",S));

and when you want make the rows, you could do this for Bars:

OLExcel.addRow(OLExcel.setCell(df.format(bidBar.getTime()),S), OLExcel.setCell(tf.format(bidBar.getTime()),S), OLExcel.setCell(bidBar.getOpen(),N), OLExcel.setCell(bidBar.getHigh(),N),
                        OLExcel.setCell(bidBar.getLow(),N), OLExcel.setCell(bidBar.getClose(),N), OLExcel.setCell(bidBar.getVolume(),N));

and for Ticks:

OLExcel.addRow(OLExcel.setCell(df.format(tick.getTime()),S), OLExcel.setCell(tf.format(tick.getTime()),S), OLExcel.setCell(tick.getAsk(),N), OLExcel.setCell(tick.getAskVolume(),N),
                        OLExcel.setCell(tick.getBid(),N), OLExcel.setCell(tick.getBidVolume(),N));

Sure, I guess you have seen my system. If not (as I have not seen his gratitude in the respective post), I suggest you see, because JForex2Excel besides being simple and powerful, it is easy to extend and / or modify.

Best regards.

Hi Sircris

Many thanks for pointing me towards your code.
I agree it is a very helpful concept with regard to collecting data from the Forex platform

My problem is slightly different....................
I am saving the historic data as a .csv file
BUT I am unable to get the Date/Time field to be recognised as a field containing a date and a time

Does that make sense?

Bob M

You should ask this in an M$ Excel related forum, as it has nothing to do with JForex...

But,
In LibreOffice whenever you open a .csv file, on the import settings page you can define what will be the separator character. You can select there space next to coma, resulting in what you want: a separated Date and Time column.
I suggest to play around in the import settings of Excel, as there should be a place where you can define the separator.

Success!

Hi Bob,

What Tcsabina says is true, if you want to import a .csv file from Excel you can use the import options, but is a bit complicated because there may be problems with the decimal format among other problems.
But I do not understand you. Why you are saving the historical data in .csv format and then convert them to Excel? When you can save the historical data DIRECTLY in. xls format and watch them in Excel without problems. Just for that I designed JForex2Excel. I recommend that you see the article, download the system, its FREE. You can run it in the Historical Tester obtaining the historical data directly in .xls format for the period you want, and then you can split Date & Time as I said above.

Anyway, if you insist to save in .csv format, I have given the way to do too. Simply use:

     public String dformat= "dd/MM/yyyy";
     public String tformat = "HH:mm:ss";
     DateFormat df = new SimpleDateFormat (dformat);
     DateFormat tf = new SimpleDateFormat (tformat);

and then use df.format() for the Date field and tf.format() for the Time field. But you may still have other problems when importing from .csv to .xls format.

Best regards.

Hi Sircris

Many thanks for your patience
The penny has finally dropped - I didn't realise that I could get the data via the Historical Tester

Excellent.................

I ran your original code and it worked............

I then modified the code with your suggestions (copy and paste) to get the Date and time separately and the result is a blank file ????

There must still be something to alter in the code?

Bob M

Hi Bob,

I am glad it was helpful.
Yes! I did this quickly and I did not try first, were missing modify a couple of lines. I've already corrected the original post. These are the lines that must change also:

this.OlExcel=new OLExcel(limit+1, 7); //1 column more (Bars)

this.OlExcel=new OLExcel(limit+1, 6); //1 column more (Ticks)

Now I've tried it and it works perfect. I attach the java file JForex2ExcelV2.java that you can compile & use directly. It generates Date and Time separately. I also included a screenshot showing the generated file.

If you have time, I would like you please can tell their experience with JForex2Excel in the article page:

https://www.dukascopy.com/fxcomm/fx-article-contest/?Jforex-Excel&action=read&id=1468

it may be useful to other users.

King regards.

Hi Sircris

Many, many thanks

You are a gem!

Bob M

Hi Sircris

I have been using your program extensively to capture historic data via the historical tester
All - OK

When I try to capture realtime data in a demo account I don't seem to get any output..........

Any ideas?

Bob M

I've tried it on a demo account and on a live account and it works perfectly.
The excel files are saved to disk after the row limit is reached. If you have a rows limit of 10,000 for example. JForex2Excel will not record a file until it generate 10,000 rows.
It is important to remember that you MUST manually stop JForex2Excel when used for live data. If you see this screenshot:



You will see that JForex2Excel was put to run 5:20:20 and was finished manually at 5:20:57. Then it will generate an Excel file with the latest data collected. If it has not generated any .xls file because it has not yet reached the rows limit, then it will generate an excel file with the data collected from the beginning until the finalization. You can manually stop JForex2Excel doing click into the Stop button with JForex2Excel selected, or doing right click over JForex2Excel and selecting "Stop"

The excel file generated from the run corresponding to the above screenshot, you can see below as proof:



If you still have problems, go to the article page:

https://www.dukascopy.com/fxcomm/fx-article-contest/?Jforex-Excel&action=read&id=1468

and download the end user version or the developer version from the Sourceforge site again.

Best regards.

Thank you.....................

I had not understood that the file is NOT written to disk until the row limit is reached.............

I am trading using a system where my requirement is that at certain times of each trading day I need to capture and analyse the last dozen or so hourly bar data

Thanks again

Bob M

Hi Sircris

I am trying to get the data 'hour' in sync with my time zone here in New Zealand
I have adjusted the following code:-

private static Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("GMT+12"));

but the data output still reads 12 hours ahead e.g. it reads hour = 20 instead of hour = 8

Any thoughts?

Bob M

Hi Bob,

Actually the ability to change the time zone was intended but NOT fully implemented in the original version of JForex2Excel. But from your question I decided to implement it. In JForex2ExcelV3 that I include in this post below, the end user has the possibility to choose the time zone through of the Parameter settings window. Works well for both historical data (Historical Tester), as for live data.
Also included a screenshot of the Parameter settings window:



The Time Zone setting usage is as follows:

Time Zone:
GMT Sign Hours : Minutes
GMT Sign Hours Minutes
GMT Sign Hours

Sign: one of
+ -

Hours:
Digit
Digit Digit

Minutes:
Digit Digit

Digit: one of
0 1 2 3 4 5 6 7 8 9

Hours must be between 0 to 23 and Minutes must be between 00 to 59. For example, "GMT+10" and "GMT+0010" mean ten hours and ten minutes ahead of GMT, respectively. If a wrong input is made, the default value is set (GMT).

Best regards.

Due to the large number of downloads of the JForex2Excel second and third version, I want to remember that these files (JForex2ExcelV2.java and JForex2ExcelV3.java) need two libraries in order to be compiled:

The OLExcel library and the Apache POI library.

All information required for installation as well as for the download of these libraries can be found on the project website at Dukascopy:

https://www.dukascopy.com/fxcomm/fx-article-contest/?Jforex-Excel&action=read&id=1468

In the developers chapter (Chapter 6)

There you can also find the information about the JForex2Excel end-users versions, which have the entire system in a single file and does not need to be compiled, (In the end-users chapter (Chapter 5))

Best regards.

Thank you Sircris

Bob M