You don't have to worry about sectors - the sector ID is automatically assigned to the symbol by the Industry ID (it is setup when the database is created - it uses the broker.sector and broker.industries files on a one off basis) - have a look at Symbols >> cAtegories >> Industries and you will see the assigned sector, relative to the industry, in a dialog box on that tabbed pane.
--- In [email protected], "brian_z111" <[EMAIL PROTECTED]> wrote: > > Louis, > > I will write a better explanation at the UKB at some stage because > the subject is obviously of general interest to a secton of users. > > I might not write up the US-Stocks database as it is too big and also > I am reluctant to post files that will become obsolete and need > ongoing updates. > > I try to give enough of an explanation so that people have the skills > to make their own updated files. > > Ami is (for me) a rather unique program and, amongst other things it > is a hybrid spreadsheet/database. > > Perhaps you are not familiar with databases. > > A database can store info on say, Brian and Louis. > > B&L would be the names in the KeyField (column) == the main > identifier. > B&L's individual attributes can be stored in additional fields > (columns) e.g. age, nationality, gender etc > This information can be stored as text or as a number, so: > > Louis, Canada, M > Brian, Australia, M > > would become: > > Louis, 2, 2 > Brian, 1, 2 > > (alphabetically Aus == 1, Canada == 2 etc) > > That is all you need to do - add numerical ID's to Jim's XL file in > place of the text ID's (ASCII loves number ID's - look on the ASCII > help section and you will see that the $Market command uses <number> > as it's argument) > > Instructions for step b)&c): > > STep 1: > > i)Open AB with US-Stocks as the database > ii)Look in AB >> symbols >> categories >> Markets and you will see > something like this (without the numbers after the names): > > Unassigned > NYSE > AMEX > Nasdaq > OTC > Market 5 > Market 6 > > The first 4 ID's have been used by Jim's setup, as Market categories, > and they have been replaced by a name. > > You need to manually enter the corresponding ID numbers into a column > in your US-Stocks XL file so that you can import the Category ID's > when you import the suffix free symbols - tehn you do that for all > Categories (Market.Group,Sector, Industry - you can do > Watchlsits,Favs and INdexes too but US-stiocks doesn't ahve them). > > To help do this here is a formula that exports the ID names/ID > numbers so that you can print them and cross check as you manually > enter them. > > Step2: > > i) make a new "Files" folder in the AmiBroker directory (text files > will be auto saved there). > > ii)Copy/paste/save the formula and run it as a scan in US-Stocks > database > > iii) change the inputs to get a text file for all categories > iv) prnt them > > The AA window will be open and empty but you will find the text file > in the Files folder (if you repeat the exercise it will overwrite the > exisiting file without warning). > > //S_ExportCategories_v4 > > //tested OK > //run with Scan current symbol and n = 1 datapoints > //first create a "Files" folder in the AmiBroker directory (using > your OS file manager) > //input == change the text filename AND the categoryParameter for > each category > //input number == change to 63 for sectors, unlimited for WL's.... > //........ignore for Favorites/Indexes AND 255 for all other > Categories > > Buy = 0; > > fh = fopen( "C:\\Program > Files\\AmiBroker\\Files\\ExportMarkets.txt", "w");//input > if( fh ) > { > > for (i = 0; i <= 255; i++)//inputnumber > > { > > ID = CategoryGetName(categoryMarket,i); //input > fputs(ID + " " + i + "," + "\n", fh ); > > } > > fclose( fh ); > > } > else > { > printf("Error opening file"); > } > > > For the US-Stocks market you will see this: > > Unassigned 0, > NYSE 1, > AMEX 2, > Nasdaq 3, > OTC 4, > Market 5 5, > Market 6 6, > Market 7 7, > > Step 3: > > i) Open US-Stocks XL > ii) Sort by Markets > iii) add a new column for Market ID (beside Markets column) > iv) manually enter (copy/paste or fill down) a 0 beside all stocks > that are sorted as unassigned (enter the zero in the MarketID column. > v) carry on to do this using 1 beside all NYSE sorted stock and > repeat for all other named Market ID's > > Step 4, 5 etc > > repeat step 3 by sorting for Groups and entering the corresponding ID > number to a Groups ID column. > > When you have done that for all the categories you want to import > then you make up a corresponding (ASCII) formats file and use that to > import the fields you want from the modified XL file. > > Note: it is a bit tedious (120+ industries) but it is actually not > difficult to implement. > > The only possible trouble will be if you try to import Names with it - > aB might not like it if the names already exist (not sure about > that) - to get around that either - delete the stocks you don't want > first or don't add the names to the suffix free symbol import (you > can add them later if needed). > > Also, if you read PaulHo's posts on ALIASES you might become > interested in importing the .OB symbols as the ALIAS for the IQ > symbol (suffix free). > > Don't worry too much about the last part - I will put an example at > the UKB. > > BTW you can actually use the formula above to help make broker.sector > and broker.industries files then put them in the AB directory then > create a new database (US_Stocks_DTN?) and then ASCII import the > whole database structure using JIms EXcel file (if you add all of the > categories ID numbers to his master XL). > > brian_z > > --- In [email protected], "Louis Préfontaine" > <rockprog80@> wrote: > > > > Hi Brian, > > > > I am sorry... I really don't understand points b) and c). I > copied all the > > .OB tickers to a new .XLS file but I am not sure what you mean by > entering > > columns for sector, industry, etc. > > > > I'm sorry if I sound like I am too slow to understand, but really > this thing > > puzzles me. I am thinking about changing my real-time provider > because of > > this... I just can't figure out how to delete the .OB. I am > seriously > > considering removing manually 50 per day and in a month or two that > would be > > over... > > > > Louis > > > > 2008/3/29, brian_z111 <brian_z111@>: > > > > > > Louis, > > > > > > Dick is quite right. > > > > > > It is easier to work with the sorted list of .OB stocks by > themselves. > > > > > > Try this: > > > > > > a) sort by .OB and copy all OB rows to a new file US- Stocks_OB.xls > > > b) enter columns for sector ID, industry ID, market ID, and Group > ID > > > c) manually enter ID numbers into each column (the numbers should > > > align to the 0 based count in AB >> symbols >> categories > > > d) save a copy as US-Stocks_OB.csv > > > e) write a formats file to suit and save it in > Programs/AB/Formats as > > > US-Stocks.format (allow for adding new symbols) > > > f) open a spare copy of US-Stocks and ASCII import US- > Stocks_OB.csv > > > using US-Stocks.format as file type > > > > > > The suffix free symbols should now be in the database in their > > > correct categories (if so you can delete the old symbols that have > > > suffexes) > > > > > > brian_z > > > > > > --- In [email protected] <amibroker% > 40yahoogroups.com>, "Louis > > > Préfontaine" > > > <rockprog80@> wrote: > > > > > > > > Hi Dick, > > > > > > > > I am not sure to understand how you would do that. I know how to > > > eliminate > > > > the .OB from the Excel file provided by Jim, but I don't know > how > > > to put the > > > > files back. > > > > > > > > I think I understand your point: there is no need for me to > start > > > again from > > > > scratch. I can use Jim database, but still, I am stuck with the > > > same > > > > problem at the end: the pink sheets tickers are from different > > > categories, > > > > industries, sectors, so how would I enter them at the correct > place > > > if not > > > > manually? > > > > > > > > Thanks, > > > > > > > > Louis > > > > > > > > > > > > > > > > 2008/3/28, areehoi <areehoi@>: > > > > > > > > > > Louis, > > > > > There is no need to convert the XL files to ASCII. Jim has > already > > > > > done this by providing the US-Stocks database. To simplify > take > > > the > > > > > US-Stocks database (intact) and copy then paste in the > Amibroker > > > > > Program files (be sure to rename the existing one to another > name > > > ie > > > > > ...US-Stocks1). When you open AB go to open database and pick > the > > > new > > > > > US-Stocks. It should work. To eliminate the "OB,s" and "Pink > > > Sheet" > > > > > go to the Excel spreadsheet and sort on the column that > carries > > > them. > > > > > Copy them and put in word (unformated) so you can make a coma > > > > > delimitated ascii file. Put that file in a blank Watch list > from > > > which > > > > > you can delet all. > > > > > > > > > > Dick H > > > > > > > > > > --- In [email protected] <amibroker% > 40yahoogroups.com><amibroker% > > > 40yahoogroups.com>, "Louis > > > > > Préfontaine" <rockprog80@> > > > > > wrote: > > > > > > > > > > > > Hi Brian, > > > > > > > > > > > > Even with the precedent method, it does not work. It works > fine > > > the > > > > > first > > > > > > time with the first group, but then whatever the number of > the > > > > > industry I > > > > > > choose, the tickers go in the first one.. or even one in the > > > fourth > > > > > group, > > > > > > with no particular reason. This looks to me like a strange > bug > > > if it is > > > > > > one! > > > > > > > > > > > > Louis > > > > > > > > > > > > 2008/3/18, brian_z111 <brian_z111@>: > > > > > > > > > > > > > > Try this: > > > > > > > > > > > > > > a) convert CSV lists >> text file > > > > > > > b) import to Watchlists > > > > > > > c) use categoryAddSymbol to move from WL to > > > Markets/Groups/Sectors > > > > > > > > > > > > > > (as per "SetUp A Custom Database/Nasdaq" at the UKB > > > > > > > > > > > > > > I think it is also possible using AFL (even within AB) but > > > others > > > > > > > could help you more with that than yours truly. > > > > > > > > > > > > > > brian_z > > > > > > > > > > > > > > --- In [email protected] <amibroker% > 40yahoogroups.com><amibroker% > > > 40yahoogroups.com><amibroker%40yahoogroups.com>, "Louis > > > > > > > Préfontaine" > > > > > > > <rockprog80@> wrote: > > > > > > > > > > > > > > > > Hi, > > > > > > > > > > > > > > > > I really enjoyed using Jim's tickers > > > > > > > > > > > > > http://icc-az.com/icc%2Daz/default.html?http%3A//icc- > az.com/icc%<http://icc-az.com/icc%2Daz/default.html?http%3A//icc- > az.com/icc%25> > > > <http://icc-az.com/icc%2Daz/default.html?http%3A//icc- az.com/icc% > 25> > > > > > <http://icc-az.com/icc%2Daz/default.html?http%3A//icc- > az.com/icc%<http://icc-az.com/icc%2Daz/default.html?http%3A//icc- > az.com/icc%25> > > > 25> > > > > > > > 252Daz/ > > > > > > > > > > > > > > > > However, I see that this is Yahoo's formatting with e.g. > > > AAAA.OB > > > > > > > for pink > > > > > > > > sheets while my provider would prefer simply AAAA. I > tried > > > to > > > > > > > remove the > > > > > > > > .OB with Excel, and did it correctly, but then I don't > know > > > how to > > > > > > > import > > > > > > > > the files WITH the categories/industries as Jim setted > them > > > up. In > > > > > > > the > > > > > > > > import wizard, there are no options for those > categories, > > > so would > > > > > > > there be > > > > > > > > another way to do this? > > > > > > > > > > > > > > > > Thanks, > > > > > > > > > > > > > > > > Louis > > > > > > > > > > > > > > > > > > > > > > > > p.s. I will try to redownload Amiquote to use Yahoo with > > > those, but > > > > > > > would > > > > > > > > enjoy using my intraday data if possible. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
