User error: "Date" not showing in explorer as date

[Henri]

As usual, something I am not understanding... but it's going "wrong".... (read, I am not getting the results I am expecting to get )

From this thread:

Working with (future) expiration dates

Hello, I am bumping into a problem that I just don't know how to fix and I am out of idea's at the moment... Any help appreciated. What I am trying to do is calculate how many trading day's are left in a futures life before it expires. I tried calculating it with "DaysInMonth" and "DayOfWeek" but that sometimes fails when there are public holidays. I tried importing the expiration date into the AUX1 field but that failed because of floating point format. What I think I can do so far is two …

I got the idea to use a foreign symbol for getting my past expiration dates.

	frg = Foreign("VX_expirationDT","C", 0) == 1;
	dt = DateTime();
	expiration_date = ValueWhen(frg==1, dt, 0);


Filter = 1;

AddColumn( expiration_date , "expiration_date", 1.4); 

that is how I put that into code. The expiration date I later want to use to calculate "day's since expiry".

However, the result is something I don't understand and have no idea how to fix.

Two things are not as expected:

1. the result is not returning a date but a very long number....

2. The numbers don't change on the 20-10-2021 even though 19-10-2021 is in the foreign symbol.

Any help pointing me in the direction of what I am not understanding appreciated, thanks in advance.

[fxshrat]

Just recent thread with same mistake here:

Display DelistingDate in exploration

PLEASE USE CODE TAGS! Also forum provides picture file upload. Third party sites may disappear in future! You have to use formatDateTime but not default format. AddColumn( GetFnData("DelistingDate"), "DelistingDate ", formatDateTime);

Use formatDateTime for DateTime arrays but not (default) formats for numbers.

frg = Foreign("VX_expirationDT","C", 0);
dt = DateTime();

Filter = frg==1;

AddColumn( dt, "expiration_date", formatDateTime);

Actually you can remove AddColumn and just use

frg = Foreign("VX_expirationDT","C", 0);
Filter = frg==1;

[beppe]

@Henri, I kindly suggest you to read carefully the documentation:

AddColumn()

In particular review the special format constants.

[Henri]

Good suggestion beppe Hopefully in the future I will realize that a format like 1.4 will not work for dates (and any of the other special formats I might use in the future ) Never thought that would be the mistake, I thought it was somewhere else in the code.

This is the code I am using now:

frg = Foreign("VX_expirationDT","C", 0);
dt = DateTime();

Filter = frg==1;

//Filter = 1;

AddColumn( dt , "expiration_date", formatDateTime); 

I understand that in the end, if all would work as expected, I can remove the AddColumn (think that is what you are referring to fxshrat) but at this moment I am using it to "see what is happening" and if I can actualy use this for what I want to do...

Dates now showing correctly because of the correct AddColumn format !

But now the next problem arises... How do I explain this....

It changes on every bar...

frg = Foreign("VX_expirationDT","C", 0);
dt = DateTime();
	
Filter = 1;

AddColumn( dt, "expiration_date", formatDateTime);

This is the result.

However, why I am creating this foreign ticker is that I want to use the expiration date to calculate how many day's have past since the previous expiration.

For example, 20-10-2021 was an expiration date, on 25-10-2021 I want to calculate how many days have past since the previous expiration (in this case 5) or on 10-11-2021 I want to do the same calculation (in that case it will be 21)

I wanted to do that by comparing the current day with the previous day.

But the expiration date changes in the exploration.... so I can not do that....

Any idea on how I could do that ?

tia

[fxshrat]

@Henri

However, why I am creating this foreign ticker is that I want to use the expiration date to calculate how many day's have past since the previous expiration.

frg = Foreign("VX_expirationDT","C", 0);
dt = DateTime();

dt_exp = ValueWhen(frg==1, dt);
dt_diff = DateTimeDiff(dt,dt_exp)/inDaily;
	
Filter = 1;

AddColumn(dt_exp, "expiration_date", formatDateTime);
AddColumn(dt_diff, "#days", 1);

[Henri]

Thank you very much. I am working on this for 6 months now.... You won't understand how frustrating it is that someone solves it in 5 minutes

Much apreciated !

So basicaly, other than my AFL knowledge, I missed that there is a function "DateTimeDiff" and you used that for the comparison. I will not bother you with the question "why did I see the changing dates" and "why do we in /indaily" it honestly is just to much for me to grasp at this moment and will come in time.

Thank you again for your effort !

PS: I read somewhere in some threads that someone was going to send you some beers, I owe you a sixpack also You are German I think, I am dutch, who knows in time I can live of to my debt