Data from CSV not parsed correctly

Data from CSV not parsed correctly - 04/18/24 10:45

Hello everybody,

I am having trouble to convert a CSV file to a .t1 file. When I print the parsed values, none of the columns is being correctly parsed and I don't receive any warning or error message.

The CSV file has following format:

Code

Time,AskPrice,BidPrice
1704213005783,49.20,49.10
1704213006179,49.22,49.13
1704213006575,49.23,49.13
1704213007576,49.24,49.13

I use following lines to parse the file:

Code

string Format = "+%t,f,f";

int Year;
for(Year = 2024; Year <= 2024; Year++) {
	dataNew(1,0,0);
	int records = dataParse(1,Format,strf(CSVFileName,Year), 0, 0);
	printf("\n%i - %d rows read",Year,records);

	dataNew(2,0,0);
	int i, N = 0;

	for(i = 0; i < records; i++) 
	{
		var time = dataVar(1,i,0);
		var askPrice = dataVar(1,i,1);
		var bidPrice = dataVar(1,i,2);
		printf("%s: %.2f, %.2f\n", strdate("%Y.%m.%d %H:%M:%S.", time), bidPrice, askPrice);
		
		T1* Quote = dataAppendRow(2,2);
		Quote->time = time;
		Quote->fVal = -bidPrice;
		Quote = dataAppendRow(2,2);
		Quote->time = time;
		Quote->fVal = askPrice;
		N += 2;
	}

	dataSave(2,strf(OutName,Year), 0, N); // store year dataset
	printf("\n%s saved",strf(OutName,Year));
}

Can somebody help me to find the problem?

I attached a sample CSV file and the complete code.

Best regards,

EC

Attached File

TQQQ_2024.csv (3 downloads)

Attached File

_EC_CSVToTicks2.c (2 downloads)

Posted By: jcl

Re: Data from CSV not parsed correctly - 04/20/24 08:32

The time conversion does not look right.

Posted By: EternallyCurious

Re: Data from CSV not parsed correctly - 04/20/24 21:39

What exactly do you think it does not look well with the time?

I use %t because the first column has Unix format in milliseconds. It is described in the "Dataset handling" section of the Zorro documentation.

Code

%t - for a date/time field in Unix format, either seconds or milliseconds since January 1,1970.

Posted By: EternallyCurious

Re: Data from CSV not parsed correctly - 04/25/24 10:20

I attached the text from the output window.

Attached picture Log.png

Posted By: jcl

Re: Data from CSV not parsed correctly - 04/26/24 11:18

The time and all other variables are zero. So the first step is debugging the conversion and checking the content of the dataset. Debugging is explained in the manual under "troubleshooting".

If you still cannot find the reason and want individual support, you could either subscribe a premium support ticket, or simply hire a programmer to write the conversion script for you. The current fee for a CSV -> t1 conversion, as to my knowledge, is 120 EUR.

Posted By: dr_panther

Re: Data from CSV not parsed correctly - 05/06/24 18:50

I've observed that when converting CSV data to T6 format, the datetime parameter appears as a decimal value. The integer part before the dot represents the number of days since January 1, 1900. The fractional part after the dot represents the time of the day in decimal format. For example, 45000.5 corresponds to 45,000 days after January 1, 1900, at 12 noon.

And keep in mind for t1, stand for time plus one data point, which is either ask or bid price, you don't have three field to populate.
Ask and bid prices are represented by positive / negative values. Check the manual for details: https://zorro-project.com/manual/en/history.htm

typedef struct T1
{
DATE time; // UTC timestamp of the tick in DATE format
float fPrice; // price data, positive for ask and negative for bid
} T1;