Importing Historical Data Timestamp Error

[KBJ]

This isn't a futures contract, this is spot forex, as quoted by Dukascopy.

Dan, Yes I know it's Forex. My understanding of Forex hours is that the market isn't open at the time of rollover....



So, although it may be a bug, it should be a bug that never gets hit unless there's bad data.

Or am I missing something here?

[JustCallMeDan]

No worries, Brett. Thanks for keeping me updated!

[JustCallMeDan]

Hi KBJ,


As I'm sure you're aware, foreign exchange rates are over the counter (OTC) instruments traded 24 hours a day across the globe. There are no real roll-over dates, although there are of course times to avoid trading. The session template within NinjaTrader that you show does not run 24/7 (Saturdays appear to be excluded) but I use the Default 24/7 session template instead, because while there may be significantly reduced trading volume, there IS still trading going on and I want my strategies to be aware of that. If the hypothesis is correct that only the least qualified traders trade when no major sessions are open, then there could even be potential profit to be made from their ill-advised trading decisions.

Also, if DST effects were consistently not being processed correctly in my local timezone, not just at the 'boundary', all data from the end of March through to the end of October could be out by an hour. That means that if I choose to backtest a strategy that aims to trade at any particular time, i.e. London Open, I could be testing on completely incorrect data for 7 months of the year, every year, which is naturally unacceptable. I haven't removed the 'offending' data lines to test this hypothesis though, and hopefully I won't have to!

[NinjaTrader_JoshP]

JustCallMeDan,

UTC is not equivalent to GMT. Please try the following option below:

PC clock = GMT (London time)
Time zone of import = GMT (London time)

Dukascopy data is GMT.

(Edited for clarity on the resolution of situation here.)

[JustCallMeDan]

Hi Josh,



Thanks for your response.

The NinjaTrader help file states:

Time Zone of Imported Data

Select the time zone of the data you are importing (not the time zone you are importing to as all imported data will always be converted to local PC time.)

Are you saying the 'Time zone of imported data' option actually refers to my PC time zone instead of the timezone of the data? As you can see, this is contrary to the help file.

The data is in UTC. My PC timezone is London time.

GMT is not, in fact, the same as London time. It simply has been replaced with UTC as the international standard. See http://en.wikipedia.org/wiki/Coordinated_Universal_Time and http://en.wikipedia.org/wiki/GMT for more information on this.


I'm sure you can understand I am becoming particularly frustrated with this. Timezones are a rudimentary part of global trading and their understanding is important. NinjaTrader fails to correctly import this UTC formatted data on my PC, in London time, according to the specification followed from the NinjaTrader help file. You may demonstrate this for yourself with the earlier 3-line file attachment.

[NinjaTrader_JoshP]

I do understand your frustration, but what I am saying is you will need to choose a correct correlation of the data to your time zone. When you do an import it will take whatever time zone you choose in "Time zone of import" and convert it to your local PC time zone. Whether your data is truly UTC or not is not something we are going to be able to analyze and figure out over here at NT. It is very clear the issue is in relation to UTC and London (which I am loosely terming as GMT) having different DST references which makes the timestamps in your data file invalid when converted between these two time zones. It could very well be your data set has already been DST adjusted or vice versa and adding another layer of adjusts on top would push timestamps to become invalid. We would have no comment on how Dukascopy timestamps their data and what procedures in relation to DST they may employ for their data sets. The solution for you though is to just simply import the data without any additional adjustments and to do that is to choose an import time zone equivalent to your PC clock timezone. Note that doing this works in this specific scenario because your data is already UTC or GMT (whichever is the case is up to you to truly identify and determine if you even care. The two time zones are very similar and it is unfortunately fairly common to be erroneously used as being interchangeable synonyms).

I am not saying GMT is London time. I am saying how the PC shows it as the time zones. Please try the import with one of the options I have laid out in my prior post and you should be fine.

[JustCallMeDan]

Okay, work along with me here. Let's try it the sensible way: my PC timezone is correct, and there can be no justified reason for a user program to necessitate changing my clock in order for it to function correctly, so I'll alter the timestamps of the data to London time, rather than UTC as it was originally.

The original in UTC is:

20031026 005900;1.1776;1.1776;1.1776;1.1776;1
20031026 010000;1.1776;1.1776;1.1776;1.1776;1
20031026 010100;1.1776;1.1776;1.1776;1.1776;1

As established, this produces an error when I try to import it as UTC

The new file with this translated to London time is:

20031026 015900;1.1776;1.1776;1.1776;1.1776;1
20031026 010000;1.1776;1.1776;1.1776;1.1776;1
20031026 010100;1.1776;1.1776;1.1776;1.1776;1

Note, only the first timestamp is affected by DST, London time 'falls back' thereafter.

So, now selecting "UTC", "(GMT) Coordinated Universal Time" or "(UTC) Dublin, Edinburgh, Lisbon, London" (why so many options if you don't intend to mislead us to think the 'UTC' would mean straight UTC with no DST?), I still get the error:

$EURUSD: Time stamp line 1 is smaller/equal than timestamp of previous line

Yes. Pretty obviously, it is. Also, pretty obviously, it's supposed to be.

[NinjaTrader_JoshP]

JustCallMeDan,

Just leave PC clock London time, select London time for "Time zone of import" and use the original file. It will import just fine. Under such a setup zero conversion is being applied and it gets imported as-is.

(why so many options if you don't intend to mislead us to think the 'UTC' would mean straight UTC with no DST?)

The options come through directly from your Windows operating system. This is not controlled by NT, but Windows.

[NinjaTrader_JoshP]

JustCallMeDan,

Here is the issue. It makes zero sense for data to be timestamped backwards in time which is exactly what you have setup. I highly doubt a data provider would provide you data timestamps in a manner that would step backwards in time like that. With that being said I took a look at Dukascopy where you said you got the data from. This is exactly what I meant earlier by your data is most likely not UTC.

Here is a direct quote from Dukascopy's website on how their data is stored.
http://www.dukascopy.com/swiss/engli...ed/historical/ (Expand the "Dukascopy Data Feed Format" section)

Candles with periods 10sec, 1min, 5min, 10min, 15min, and 30min are divided into days. A new day’s file is created at 24:00 GMT.

At the beginning of this whole ordeal I've already stated GMT does not equal UTC. I have mentioned the unfortunate ambiguity in how these things are used interchangeably. In this case here it is very clear to me your data is actually provided in GMT Greenwich Mean Time. As seen from my screenshot in one of my earlier posts, this is the London tagged time zone. As such is the source of your data import it makes zero sense to try and import that data as UTC time zone. You needed to import it with London time as I have outlined and when done so in this manner everything works perfect. There is never a need for you to try and manually adjust timestamps on data provided.

Here is further proof directly from the mouth of Dukascopy support that their data is GMT not UTC. http://webcache.googleusercontent.co...www.google.com

So to wrap things up. Only thing that is important is to choose a "Time zone of import" that matches the time zone of the data set. In this case, London time.
Data = London time
PC clock = London time (doesn't even matter what time zone you set here)
Time zone of import = London time

[JustCallMeDan]

I understand we are mis-communicating with regard to timezones, and that there is a certain level of ambiguity with regard to their usage, but you also seem to have some things confused.

Here's an excerpt from Wikipedia (http://en.wikipedia.org/wiki/Coordinated_Universal_Time), to which site I directed you earlier. This is information is reflected in other sources:

Coordinated Universal Time is a time standard based on International Atomic Time (TAI) with leap seconds added at irregular intervals to compensate for the Earth's slowing rotation.[2] Leap seconds are used to allow UTC to closely track UT1, which is mean solar time at the Royal Observatory, Greenwich.
Since the difference between UTC and UT1 is not allowed to exceed 0.9 seconds, if high precision is not required, the general term Universal Time (UT) may be used.[3]
In casual use, when fractions of a second are not important, Greenwich Mean Time (GMT) can be considered equivalent to UTC or UT1. Saying "GMT" often implies either UTC or UT1 when used within informal or casual contexts. In technical contexts, usage of "GMT" is avoided; the unambiguous terminology "UTC" or "UT1" is preferred.[3]
Time zones around the world can be expressed as positive or negative offsets from UTC as in this list; UTC replaced GMT as the basis for the main reference time scale or civil time in various regions on 1 January 1972.[4]

GMT is NOT commonly mistaken among traders as London time, especially those trading forex. Dukascopy are falling into the far more common ambiguity between GMT and UTC, which are after all only fractions of seconds apart, and this can be simply proven by the absence of any DST 'spring forward' or 'fall back' in the timestamps in their data.


You say:

Here is the issue. It makes zero sense for data to be timestamped backwards in time which is exactly what you have setup. I highly doubt a data provider would provide you data timestamps in a manner that would step backwards in time like that.

I never claimed otherwise, throughout I have asserted that Dukascopy data is in UTC. I only modified the data when I was unable to import the data by choosing "UTC" or "(GMT) Universal Coordinated Time", which is the logical choice to make in this dialogue.

Having followed your earlier advice I can claim that the data successfully imported without throwing any error, by claiming it is "(UTC) Dublin, Edinburgh, London, Lisbon"

So why doesn't "UTC" or "(GMT) Coordinated Universal Time" work? After all, the timestamps aren't actually London time, they're UTC...

[NinjaTrader_JoshP]

So why doesn't "UTC" or "(GMT) Coordinated Universal Time" work? After all, the timestamps aren't actually London time, they're UTC...

Maybe your Windows version is different than my Windows version, but it is my understanding that when someone tells me data is Greenwich Mean Time, I choose the time zone that also says Greenwich Mean Time on it. On my Windows, that time zone would be the one which is also tagged as London as seen in my attached screenshot. Which time zone Dukascopy is actually using can only be answered by them. I have provided you with my interpretation of the information I found available for what they offer, but let us entertain the thought that when Dukascopy says GMT they actually mean UTC. What that would mean is on dates when London goes from BST to UTC (like Oct 26, 2003), London has two hours of timestamps for the 1AM hour. What that means is taking data from UTC and trying to convert it to London timestamps would never fly on those hours. It is impossible to provide two hours of the same timestamp as required by the London timing.

Let us then instead take a step back and evaluate what exactly is going on here. I took a deeper look into the file format that Dukascopy is offering. If you take a look at the data file, all those hours in question are actually when the market isn't even trading. The volume is all 0. This is a concept that is inherently incompatible with NinjaTrader. When there is nothing going on, NinjaTrader will leave it empty and not try to populate in last traded price and a 0 volume just to have a data point for every single minute. NinjaTrader's concept is to only contain data points for when there is actually something occurring while Dukascopy's concept appears to be to have something for every single minute and just represent it with 0 volume. What that means for you is that you really want to actually delete all those 0 volume data points out of the Dukascopy data before importing it. After removing all those 0 volume data points you would have no problem importing the data with UTC selected instead of London.

Edit: I have confirmed with Dukascopy that this is indeed the style of data they provide. It will be a complete offering of data points for every single minute of the day and just tagged as I suspected with 0 volume when nothing is going on.

[JustCallMeDan]

Maybe your Windows version is different than my Windows version, but it is my understanding that when someone tells you data is Greenwich Mean Time, you choose the time zone that also says Greenwich Mean Time on it.

I can understand how there is confusion here, in London (and especially among forex traders) we would be careful to make distinct references to either GMT, UTC or London time. Only GMT and UTC would be considered interchangeable, for all intents and purposes.

On my Windows, that time zone would be the one which is also tagged as London as seen in my attached screenshot.

Also granted, Windows can misleadingly use things such as 'GMT' and 'UTC-5' to indicate the timezones of countries which observe such an offset when DST is not in effect.

Which time zone Dukascopy is actually using can only be answered by them. I have provided you with my interpretation of the information I found available for what they offer...

To be blunt here, you found a Google cached copy of a deleted forum post in which the Support respondent simply confirmed: "Dukascopy works based on GMT time." and did not address later concerns on DST.

..., but let us entertain the thought that when Dukascopy says GMT they actually mean UTC.

I have now justified this several times over, the data includes timestamps which simply do not exist in London time, and only in UTC. This is due to the 'spring forward' that would have to be present if the timestamps were London time.

What that would mean is on dates when London goes from BST to UTC (like Oct 26, 2003), London has two hours of timestamps for the 1AM hour. What that means is taking data from UTC and trying to convert it to London timestamps would never fly on those hours. It is impossible to provide two hours of the same timestamp as required by the London timing.

And that would be valid local time, as expressed in my very first post. Naturally, this is another justification for the convention of timestamps in files to be in UTC, as it does not require a further variable to be recorded to indicate whether DST is in effect or not.

Let us then instead take a step back and evaluate what exactly is going on here. I took a deeper look into the file format that Dukascopy is offering. If you take a look at the data file, all those times in question are actually when the market isn't even trading. The volume is all 0. This is a concept that is inherently incompatible with NinjaTrader. When there is nothing going on, NinjaTrader will leave it empty and not try to populate in last traded price and a 0 volume just to have a data point for every single minute. NinjaTrader's concept is to only contain data points for when there is actually something occurring while Dukascopy's concept appears to be to have something for every single minute and just represent it with 0 volume. What that means for you is that you really want to actually delete all those 0 volume data points out of the Dukascopy data before importing it. After removing all those 0 volume data points you would have no problem importing the data with UTC selected.

Of course, and I understand this. Once I am happy that NinjaTrader is correctly importing UTC timestamped data to my time zone I will delete these zero volume lines. But to disregard the error occurring at the boundary is, in my view, incredibly short sighted. You are assuming that there could be no useful information for trading at this time and using that as justification for not looking closer. It could be indicative of a more important issue, and in future I would expect sources of 24hr quotes to emerge that WILL have useful data at this time.

If I import UTC data as "UTC" or "(GMT) Universal Coordinated Time", I am greeted with an error. If I MISLEAD NinjaTrader into thinking this data is in fact London time by selecting "(UTC) Dublin, Edinburgh, Lisbon, London", I can import the data without any reported error.

This means NinjaTrader is presented with timestamps that should not even exist in that timezone, and proceeds anyway. An absence of an error message is certainly NOT the absence of an error, and I am now concerned as to what exactly the Time[x] values represent - are they the user's local timezone with no DST? Are they UTC? Are they local time?

According to Microsoft .NET documentation:

... in a time zone-aware application, you must rely on some external mechanism to determine the time zone in which a DateTime object was created.

So, the DateTime objects returned by Time[x] are in what time zone?

[NinjaTrader_JoshP]

Time[x] is local PC time zone. All data stored in NT is always stored as local PC time zone and accessing it anywhere in the application will yield it with local PC time stamps.

The fact of the matter is you are importing invalid data as outlined from my prior posts. Such a data set simply will not fly. With valid data you will experience no issues. Everything in NT for importing is working 100% accurately exactly the way it should. All time zone conversions are done accurately. There is simply no way to store two hours worth of 1AM timestamps when converting UTC to London while London switches DST settings. Furthermore, this is not a real-world scenario as already outlined on the invalid 0 volume data.

At this point in time this has become a purely academic exercise with no benefit to anyone. We will not be changing our historical data architecture just so it can hold 0 volume synthetic data that does not actually exist on hours nothing was trading at this point in time. Nor will we be trying to create valueless algorithms to accommodate for this fake data with duplicate 1AM timestamps caused by DST switching. What I will do though is discuss with development to see if we can get some intelligent detection to determine this scenario for an easier to understand error log message.

Furthermore, saying 24 hours data providers would provide useful information at the time in question is not realistic. Forex markets close Friday ~4PM Eastern and begin Sunday 5PM Eastern. That converts to Sunday 9PM UTC which is well past the time in question of Sunday 12-1AM UTC. The only date/time in which DST switches would ever occur on is a point in time you will never have any real data (unless the forex markets decide to change their hours which is highly unlikely).

This case is now closed. To recap, you can use your Dukascopy data in UTC time zone by remembering to first filter out and remove the 0 volume data.

[JustCallMeDan]

Hidden in this response is the first indication you have made that the reason I cannot import UTC data is not because of somehow invalid timestamps or corrupt data, but actually because NinjaTrader deliberately and knowingly omits an hour of time either immediately before or immediately after the period whereby DST comes out of effect, therefore resulting in any data relating to this period causing an error, while happily importing data corresponding to local times which do not exist where DST comes into effect.

Armed with this information, I can now understand how deleting data corresponding to the 'fall back' period is all I can do to circumvent the issue, but in my opinion this is a lazy workaround for a platform that I believe should have the capability to deal with all UTC times, and does not give meaningful log errors for this particular shortcoming.

When applying your earlier advice, importing data with UTC timestamps via the choice of "(UTC) Dublin, Edinburgh, Lisbon, London", as feared this resulted in invalid data, consistently one hour out during British Summer Time. Thankfully, this later advice will instead achieve the desired result.

Workaround in hand, it is still my opinion that NinjaTrader, like all standard multi-time zone software, should have been built on storing dates in UTC, and I am unpleasantly surprised to find that it cannot correctly handle all valid UTC times.

Thank you for your help, I hope you consider switching to UTC in your DateTime structures in the future.



Dan

[JustCallMeDan]

Hi again Josh,


I realise it will appear that I am flogging a dead horse here, but I have now confirmed that there is at least one real-world situation in which the limitations of NinjaTrader would result in an inability to process valid and useful data:

Oanda is a true 24/7 forex broker (as you may see here: http://www.oanda.com/corp/story/innovations) and they have confirmed to me that their fxTicks data service would include valid quotes from the weekends (although, naturally they discourage trading then due to illiquidity and significantly increased spreads.) I would either be unable to backtest weekend trading strategies on this data in general, or would have to omit dates where DST comes into or goes out of effect.

This isn't to suggest I would seriously intend to trade this data, just to illustrate that it exists, is valid, and not compatible with NinjaTrader as per your response above. Also, that NinjaTrader would not throw any errors if I had incorrectly chosen a local timezone as the timezone of the data, even if dates and times within that time zone are in fact invalid (i.e. due to the 'spring forward')

Think of this as an attempt on my behalf to justify why switching to UTC would be a good idea in future versions of NinjaTrader, to communicate my desire for clearer error messages at time of import and a clearer help file with regard to what timestamps may and may not be imported with UTC data files, and additional error checking during local timezone imports to deal with invalid times, rather than an attempt to frustrate and annoy the patient and helpful support staff of these forums

Again, thank you both to Brett and Josh for helping hammer this frustrating issue out, I appreciate your responses despite our mis-communications!


Dan