Exporting Symbol data to a ".csv" file

Hi guys,

I am trying to export Ticker, FullName, SectorID, and IndustryID for each symbol in one of my databases to a .csv file. The used code is below.

if ( Status("actionex") != actionExEditVerifyFormula)
	{
	fh = fopen( "c:\\_AmibrokerDataExport\\Sectors&IndustriesUS_AB_Yahoo.csv", "a" );
	if (fh)
		{
		ticker = Name();
		FullName_ = FullName();
		Sector = SectorID(mode=1);
		Industry = IndustryID(mode=1);

		qs = StrFormat( "%s=%s=%s=%s\n", ticker, FullName_,Sector,Industry);
		fputs( qs, fh );

		fclose( fh );
		}
	} 

Buy = 0;

I was having issues when importing that ".csv" file to Excel, so finally I have used "=" as the delimiter (because "," sometimes is within FullName and Industry).

I have noticed I am forgetting something in my code, because it seems, when writing data for a symbol, sometimes data for two different tickers is merged into a single line. I have identified the issue because that usually generates more delimiters per line than expected.

I'm a bit puzzled because when I run the code using a SCAN on the database fed by Norgate, the file is generated correctly. However, it is generated incorrectly when I run it on the database fed by Yahoo (using Update Symbol Lists and categories).

Someone knows what am I overlooking in my code?

Thanks a lot,

if you are using modified code from here
AmiBroker Knowledge Base ยป How to export quotes to separate text files per symbol

Your line is ... riesUS_AB_Yahoo.csv", "a" ); ...

You missed a very important thing!
Now you are running multi-threaded Analysis scan and expecting it to APPEND to single file.

Most likely Norgate output is correct is because data fetch has a delay and appears sequential in some way.
Data from local DB is super fast for the yahoo downloaded one.

You can use #pragma maxthreads 1 and run one thread or write code so that many threads dont attempt to randomly write/append to single file or use separate files and merge later.
Ofc last option wont make much sense as there is very less data

2 Likes

A better option might be to simply create an Exploration and save the result as a CSV.

1 Like

@nsm51 I thought about multi-threaded when I detect my issue but I was not sure because I didn't understand why it worked with the Norgate database.

@mradtke That was my first attempt, but I couldn't find where to choose a delimiter other than ','. But I think I will take this option again adding my own delimiter directly in the Explore view. Then I will delete comas generated by Amibroker when exporting CSV file.

Thank you very much to both of you.

You could just remove or replace the commas from FullName and Industry using the StrReplace() function. That way commas in those fields wouldn't conflict with the delimiter.

1 Like

@mradtke, I typically use this function, but in this case, I want to keep the sector and industry numbers exactly as they are. I will add ";" to each column and then replace ",;" with ";" directly using Notepad.

if you are using the code method to export, you can export the string and enclose with extra pair of double-quotes. That is how the comma can be retained without affecting the formatting.

in StrFormat() you have to escape them "\"%s\",\"%s\",\"%s\",\"%s\"\n"
You dont need to change comma delimiter to another character.

1 Like

@BernieTGN,
You can try using this.

fputs(Name() + "," + FullName() + "," + SectorID(1) + "," + IndustryID(1) + "\n", fh);

@nsm51,
I have avoided using #pragma maxthreads 1 with these.

if (fh) {
	fput.....
	fclose(fh);
}
else {
	// If fh is null, do fopen untill fh is not null
	do 
		Open file for append.
	while (!fh);
	if (fh) {
		fput....
		fclose(fh);
	}
}

I dont think your approach resolves a race condition.
You can post full code that you tested with.

You should read knowledge base here

fopen function has share aware option.

https://www.amibroker.com/guide/afl/fopen.html

1 Like

Please try these codes. The number of records in Exploration Report should match those in ohlcv.txt.


start_bar = Lookup(BarIndex(),DateTimeConvert(2,Status("rangefromdate")),1);
end_bar =  Lookup(BarIndex(),DateTimeConvert(2,Status("rangetodate")),-1);
Date_time = DateTime();
O = Open;
H = High;
L = Low;
C = Close;
V = Volume;
Filter = 1;

SetOption("NoDefaultColumns",true);
AddTextColumn(Name(), "Ticker");
AddColumn(DateTime(), "Date", formatDateTime);
AddColumn(Open, "Open", 1.2);
AddColumn(High, "High", 1.2);
AddColumn(Low, "Low", 1.2);
AddColumn(Close, "Close", 1.2);
AddColumn(Volume, "Volume", 1.0);

if ( Status( "stocknum" ) == 0 ) {
	//Write lable once at the first line.
	fh = fopen( "c:\\ohlcv.txt", "w", shared = true);
	fputs("Ticker,Date,Open,High,Low,Close,Volume\n", fh);
} else
	fh = fopen( "c:\\ohlcv.txt", "a", shared = true);

if (fh) {
	for (i = start_bar; i<= end_bar; i++) {
		mdy = DateTimeFormat( "%m/%d/%Y", Date_time[i] );
		fputs(Name() + "," + mdy + ",", fh);
		fputs(NumToStr(O[i],1.2,False,True) + "," + NumToStr(H[i],1.2,False,True) + "," + NumToStr(L[i],1.2,False,True) + "," + NumToStr(C[i],1.2,False,True) + "," + NumToStr(V[i],1.0,False,True) + "\n", fh);
	}
	fclose(fh);
} else {
	// If fh is null, do fopen untill fh is not null
	do 
		fh = fopen( "c:\\ohlcv.txt", "a", shared = true);
	while (!fh);
	if (fh) {
		for (i = start_bar; i<= end_bar; i++) {
			mdy = DateTimeFormat( "%m/%d/%Y", Date_time[i] );
			fputs(Name() + "," + mdy + ",", fh);
			fputs(NumToStr(O[i],1.2,False,True) + "," + NumToStr(H[i],1.2,False,True) + "," + NumToStr(L[i],1.2,False,True) + "," + NumToStr(C[i],1.2,False,True) + "," + NumToStr(V[i],1.0,False,True) + "\n", fh);
		}
		fclose(fh);
	}
}

Instead of complex code that attempts wait to lock file for writing, you should just use

#pragma maxthreads 1

Since there is only ONE physical file and there can be only ONE thread writing to file at any time, it is pointless to run in many threads then wait for ability to open one shared file. It won't be any faster than just run one thread.

So, use pragma and you won't need to worry about shared access.

The simple solution of using #pragma gives you simplicity and pretty much same speed as writing to the file is pretty much the slowest part of the whole thing. Another benefit is that you would have file sorted in alphabetic order.

2 Likes

This topic was automatically closed 100 days after the last reply. New replies are no longer allowed.