NOT Failing to import AUX1 correctly

[Henri]

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 correctly and the rest is rounded ?

any help or pointers for the right direction appreciated.

An example of the file can be found here:

Connecting...

Thanks in advance !

[fxshrat]

Recommended readings

About floating point arithmetic

Import data with 5 digits

That is common misconception. Actual precision of IEEE representation depends on actual number stored. The numbers that are binary (power of 2) fractions can go beyond 8 significant digit, example: The numbers: 100.03125 (8 significant digits) 100.015625 (9 significant digits) 100.0078125 (10 significant digits) are represented accurately in single precision floating number. Why? Because the fractions presented in the numbers above happen to be negative powers of 2 and have limited binary r…

https://www.h-schmidt.net/FloatConverter/IEEE754.html

[Henri]

Thanks, I now see that in some equity curve data Amibroker does the same thing, the first 7 numbers show and the rest is that "floating point" thing.

So apperant I can not store a date in the AUX1 field or a number greater than 9.999.999 without loosing precision.

Just for my understanding of all this, why is it that a date, like 12-12-2020 can be stored without being "rounded" ? Probably this is not a numerical field, it's not a number and therefore does not have this problem or something ?

Anyway, thanks for directing to that thread.

[fxshrat]

DateTime column is special field and is 64-bit.
Other fields are single precision floating point.

[Henri]

Aha, clear, thanks for the answer.

[Tomasz]

The values are IMPORTED CORRECTLY.

Now all data fields (except datetime) are IEEE754 international standard single precision floating point. Read this: http://www.amibroker.com/kb/2010/07/20/about-floating-point-arithmetic/
to learn what is the precision of IEEE floating point number (it is often quoted that it offers 7 significant digits but it is simplification)

The number written as 1.23e+9 is exactly the same number as 1230000000.

What you see on screen is NOT actual number. It is result of formatting. Large numbers are by default formatted using SCIENTIFIC notation but it DOES NOT MATTER because computers do NOT store values like you see them on screen.

You need to understand that computers DO NOT work with decimals. They work with BINARY and you can use function like StrFormat or WriteVal or Num2Str to format the number anyway you like - but again computer only works with binary. Binary representation is standard IEEE754 and practically all modern computers and all modern programs work with same standard (unless they resign to use hardware FPU/SSE).

[Henri]

If I understand you correctly, the data is stored in to the AUX field exactly as it was written in the csv file it just does not show it correctly on the screen ?

I know that the numbers I try to import into AUX1 for date 16-03-2021 is 20210317

Even though it shows on screen in the column as 2.021032e+07 and in the editor as 20210320 it does hold the value 20210317 ?

And even though it does not show correctly, I can do calculations with it that will be correct ? Would it show up correctly in an exploration ?

tia

[Tomasz]

Re-read my answer. Computers do NOT work with decimal numbers.
The import procedure stores data in computer memory in binary (not decimal) form. And such binary form is subject to IEEE754 specification with regards to precision and rounding.

The following code uses ONE NUMBER (that has only ONE value) and prints it in a number of ways depending on formatting string:

number = 20210316;
 
for( i = 1; i < 10; i++ )
{
    printf( "%." + i + "g\n", number );
} 

Result is:

2e+007
2e+007
2e+007
2.02e+007
2.021e+007
2.021e+007
2.02103e+007
2.021032e+007
20210316
20210316

These all texts represent just ONE value, yet text you see on screen is different, because requested format is different. Depending on how many places you have for the display, computer would display ROUNDED decimal representation of actual number. But the number stored in computer is NOT decimal, it is binary and its binary value for all those outputs is the same.

Computer holds in this case binary number of: 01001011100110100011000101000110 or hexadecimal: 0x4b9a3146

Source: https://www.h-schmidt.net/FloatConverter/IEEE754.html

(Note: Now if you enter 20210317 in that calculator you will learn than its single precision float representation is the same). Bottom line: don't assume that floating point numbers work as integers because they don't. There is 23 bits of mantissa in single precision float and that limits the resolution of integer values. Don't use YYYYMMDD numbers to represent dates. DateTime stamps generally need way more space (64 bits) and DateTime field gives that and you should ONLY use DateTime field for dates. If for some reason, you still want to abuse Aux field into storing non-numeric data such as date, what you can use is DateNum, which is YYYMMDD (where YYY is year-1900) or even YYMMDD

Funny thing is that even some "engineers" are lost and some answers (especially top voted) on StackExchange are plainly wrong/incorrect in many details, and only some lowest-voted answers are actually correct such as:

When conversion from Integer to Single may lose precision

[Henri]

Thanks for the explanation. I do understand the problem......

@Tomasz

DateTime stamps generally need way more space (64 bits) and DateTime field gives that and you should ONLY use DateTime field for dates. If for some reason, you still want to abuse Aux field into storing non-numeric data such as date, what you can use is DateNum, which is YYYMMDD (where YYY is year-1900) or even YYMMDD

That is the problem I have. This is the way CSIData provides it to me. In that exact format. And they do not provide a way to change that format unfortunatly. They write that in a csv file and that's all I can get.

I will happily use any other format or datenum, or anything that can help me... Datenum does seem the way to go, I just have to think about a way to convert this csv provided YYYYMMDD format to datenum before or during importing the csv file into my AmiBroker database....

Will read up on the links you posted.

[Tomasz]

You can use YYYYMMDD format if only you import dates into correct field, i.e. into DateTime field.

As far as I know CSIData has AmiBroker export built-in into their Unfair Advantage product, so you don't really need to do anything - just use their exporter.

If for some reason you want to import data manually, you should import DATE column into DATETIME field, not into Aux.

Aux fields are NOT for dates.

Use appropriate field designed to be used with DATES.

Just select DATE_YMD in correct column in the Import Wizard

Import Wizard

and it will import YYYYMMDD date stamps perfectly fine.

Also Aux fields are only for quotation-like data that change every bar. For single date (like expiration date that you mentioned in other thread) you should use single-date fields like Dividend Date or Split Date.

[Henri]

Date is imported correctly, what I was trying to do is import the expiry date for a future (or option) This is always a date in the future and CSI writes that to a csv export... in that strange format unfortunatly.

That way you can calculate day's till expiry.

Working with (future) expiration dates

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: and this: You can then access that dates using GetFnData function

Have not read everything completely yet but I think this will be the solution.

Thanks for all the help, everything a lot clearer now.