Populating new database from an excel csv ASCII file

Can you help me guys giving me some light of what Im doing wrong from here?

From documentation, manuals and forum posts reading, Im currently done a simple process creating within excel a list of symbols from esignal with some classification I would like to create and maintain inside AB database but still having some issues:

  1. Tools -> Cleanup Database (To start clean and prone to previous forgoten mistekes I could made as this is a new databse).
  2. Created a .format file and added to import.types list for quick File -> Import ASCII process:
# Format definition file generated automatically
# by AmiBroker's ASCII Import Wizard
$FORMAT Ticker, SectorName, IndustryName, Market, Currency, Skip, Group, Skip, FullName, GICS, Skip
$SKIPLINES 1
$SEPARATOR ,
$AUTOADD 1
$OVERWRITE 1
$NOQUOTES 1
$OVERWRITE 1
$CLEANSECTORS 1
$SORTSECTORS 1
$DEBUG 1

A stripped down version of my text (csv) exported excel symbols file looks like follows:

SYMBOL,SECTORNAME,INDUSTRYNAME,MARKET,CURRENCY,INDEX,GROUP,COUNTRY,FULLNAME,GICS,ICB,,,
$ISE_02-MEX,Materials,,BMV,MXN,,Index,Mexico,,15,,,,
$ISE_03-MEX,Industrials,,BMV,MXN,,Index,Mexico,,20,,,,
ATS A0-FX     ,,,FOREX,,,Currency Pair,,,,,,,
AUD A0-FX     ,,,FOREX,,,Currency Pair,,,,,,,
COLLADO-MEX,Materials,,BMV,MXN,$ISE_02-MEX,Common Stock,Mexico,,15,,,,
CONVER\A-MEX,Materials,,BMV,MXN,$ISE_02-MEX,Common Stock,Mexico,,15,,,,
LAMOSA-MEX,Materials,,BMV,MXN,$ISE_02-MEX,Common Stock,Mexico,,15,,,,
0R87\N-MEX,,,BMV,MXN,,SIC,,,,,,,
AA1-MEX,,,BMV,MXN,,SIC,,,,,,,
AABA-MEX,,,BMV,MXN,,SIC,,,,,,,
  1. After doing File -> Import ASCII, I still have this issues unresolved:
  • All Import symbols done correctly without any errors on log file.
  • No Groups added at all. (Have read something that this must be done manually at Symbols -> Category window before import process…?) I want to define type of tycker on group, like if they are Common Stock, Currency Pair, an Index (Not know if “Is an Index” on the category pane window treat the symbol as a GroupID(), etc…
  • Same thing as b but with Market list.
  • Sectors are created from within import process, but not populated with stocks.
  • e) GICS classification whatsoever, it done correctly, as they have been populated as description on import list. Can I use them to write Sectors / Industry fields by a AFL in an exploration process ?

As an additional quesiton, I have not found if it is possible, and how to populate some fields from within this process like County and from category info, how to mark a symbol from “Is Index?” as true.

What Im doing wrong…?

Thanks in advance.

Cheers.

1 Like

@xel.arjona, in great part, you already answered yourself! Group and Market (and Watchlist) expect a number (so you need to create them in advance in the UI). (Would be nice to modify it in future versions to work like the recently introduced functionality of IndustryName and SectorName).

As you said, sectors and industries lists are properly created; where these properties were defined in ASCII file, you can check the correct assignment on the DB to a certain ticker using the “Window” / “Symbol Information” window. The “Symbol” / “Organize Assignments…” menu options that show the “Assignments organizer” window does not show association to Sectors but only to Industries.

Re Country, etc. you are right (seems to be missing from the list of the fields that could be individually imported/assigned). Same for “Web ID”.

Maybe @Tomasz will explain us better the issues and the reasons for any missing fields.

2 Likes

Thanks for your reply @beppe I started doing manual work for GroupID creation (just renaming) and redo the excel table to classify groups by ID number instead of name. The only issue still not importing, are the Sectors as with Names. CSV’s from yahoo in doc’s examples put fields inside quotes “_”, could this be a probably issue…?

Oh, and the other thing, can I classify stocks as Index (or favourites) from import process…?

Thanks again and good day!

1 Like

Re the sectors in quotes I do not know: just try and if it does not work, redo the procedure after removing (find/replace) them from your CSV file (just be sure that between any " " there is no default separator, otherwise replace them with another char like a space or a dash).

Unfortunately, Index and Favourite are not listed in the ASCIIe import specs

https://www.amibroker.com/guide/d_ascii.html

But they are available as properties of the stock object using the OLE automation, so with an additional script (and extra work!), you can set them programmatically (if there is a better way let’s hope @Tomasz will teach us before spending time on such a task).

https://www.amibroker.com/guide/objects.html

Curiously the “Country” field is missing there too…

3 Likes

Had to make some arrangement’s to calcsheet for csv exporting adding groups manually and treat them as ID numbers, then from within Ami’s UI edit names for gropus and verify number ID to make it coincide with csv. Regarding SectorName issue was solved to copy-paste (one-on-one as documentation says) SectorNames to IndustryNames, as long as don’t have and will not use Industry classifications, just sectors. All categorization now looks what I wanted to.

Thanks for your support!

1 Like

Hello anyone,

I've been looking for historical post about importing ascii into AB but most of the posts I saw were about EOD quotes. I am from Philippines and there are some tools already available that we can use in importing EOD quotes into AB.
My problem is how to import fundamental data into AB as this is not supported by current tool we have. I will appreciate any idea you may share where I should start. I have a hint that I could use the import wizard but not sure how to do it. I have little knowledge on programming but not an expert like most of people here in this forum.

Thank you.

@Dquint, AB supports Aux1 and Aux2 in the database directly. If you have only 2 fundamental fields, then you can import them. Beyond the 2 fields, you will need to find a way to supplement AB's default database.

You may also need to spend some time with the manual to understand the power and flexibility of the Import from CSV files that AB provides.

https://www.amibroker.com/guide/d_ascii.html

Beyond 2 fields you can just use artificial extra tickers. That way you can have infinite number. Also worth noting is that we are speaking about DATA SERIES (historical data). As long as current fundamental data are considered, AmIBroker natively supports WAY WAY more fundamental fields out of the box, see http://www.amibroker.com/guide/h_fundamental.html

Hi Tomasz, snoopy.pa30, and awilson;

Thank you for tour replies. I will read and try them until I able to solve. Im sure the solution is just lurking around the corner. I will figure it out. Thank you for your time, concern for guidance and effort to reply.
More power to Amibroker!

Best,
Dave