Working with (future) expiration dates

[Henri]

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 things, hopefully someone can tell me that one of the two is the best solution or someone has some other way that might be better.

1. Create a CSV file with the current date and the expiration date. I can then read this file from my afl code and use the calculations.

2. I store all past and future expiration dates in my afl file and read through that. This will create a very long afl file but it might be the solution.

Does anyone have any idea's about this ? I have never done something like this and honestly, have no idea which route to take.

Thanks in advance !

[fxshrat]

@Henri

I tried importing the expiration date into the AUX1 field but that failed because of floating point format.

Simply store the dates to DB as artificial ticker with ticker name as prefix:

YourSymbol_ExpirationDT or similar...

Then use Foreign function when main ticker is selected.

frg = Foreign(Name()+"_ExpirationDT","C", 0) == 1;
Plot(frg, "Expiration TRUE", colorRed, styleHistogram );

to get date

dt = DateTime();
frgn_date = ValueWhen(frg==1, dt, 0);

See ValueWhen for function arguments.

---

ASCII data example:

20200415,1
20200520,1
20200617,1
20200722,1
20200819,1
20200916,1
20201021,1
20201118,1
20201216,1
20210120,1
20210217,1
20210317,1
20210421,1

---

Or since it is related to this thread

Failing to import AUX1 correctly

Hello, I am trying to import some data into the AUX1 field but for some unknown reason, I am failing misserably. This is the last line of my file: 2021-03-25, 22.9000, 24.5000, 22.1500, 22.3056, 104392, 20210421,202104 Date, Open, High, Close, Volume, Expiry date, Contractnumber My import fails on the expiry date. In the quote editor is shows 20210420 and in the data fields it shows 2.021042e+07 During the import it looks like ony the first 6 characters are imported corr…

Store the dates to Aux1 of main ticker as datenum values so e.g. 1210421

then in AFL

exp_dt = DateTimeConvert(2, Aux1);

[Henri]

Thanks again.

From Tomasz I get the impression that the numbers are allready stored correctly, just not showing correctly, but using the datenum value sounds like the solution to me if for some reason the other thread can not be used.

If I understand how AmiBroker works, I can write an afl that can handle the import of my csv file, in that afl I can convert the expiry date column to datenum and then take it from there. Not sure if the normal importer can handle the conversion to datenum....

Thanks again for your help !

[Tomasz]

Aux fields are not for dates.

For dates you have to use designated field that can hold actual date. Aux is not for that.

If you want to store contract expiration date you can for example re-use dividend date for that purpose (because contracts don't pay dividends) or split date.

Both (dividend date, split date) can be imported using ASCII importer, see:

Import from ASCII file

and this:

Using Fundamental data

You can then access that dates using GetFnData function

AFL Function Reference - GETFNDATA

[Henri]

Have not read it all completely yet but this sounds like the way to go to import other dates ! import wizard did not show all the options that are apperantly available.

Thanks again !