Tools \ Update US symbol list and categories inconsistencies

Hello -

The function “Update US symbols list and categories” in the Tools drop menu down is giving me incorrect results. I have tried this on several different Amibroker databases, before and after doing a historical update (Yahoo). The inconsistencies are in the sector and industry data items. I noticed the errors in several ETFs (XLK,QID,SDS,IEV and others). Prior to running update, sector industry was (Finance / ETF), afterward it was (Energy / Coal Mining). I saved the database and re-ran the update and the above symbols now show (Technology / Semiconductor).

With all of the inconsistencies from Yahoo, I expect this is another one. I wanted to post this to give everyone a heads up that this function is giving inconsistent results.

If anyone has a recommendation as to how to get correct data into my database(s), it would be appreciated.

BD_TX

Update US symbol list & categories function does NOT use Yahoo as a source. It uses official Nasdaq and NYSE sites.
If you searched the forum prior to posting you would find this: Tools \ Update US symbol list and categories and this: List of US stocks with sector information

Since apparently some people missed that - It should be well noted that this function covers COMPANIES only, i.e. common stocks of companies, those which are listed on official Nasdaq page as mentioned in other thread
http://www.nasdaq.com/screening/company-list.aspx

Exchange Traded Funds, or ETFs are not on that page. So there is no "error", these items simply are NOT there.

Also note that Update US symbol list and categories WIPES category structure and creates FRESH NEW one.

So all your existing assignments are wiped and new ones are created. That is OK if you only use symbols managed completely by Update US symbols and categories.

You are getting A WARNING that is prominently displayed that says it will make permanent changes
image
so don't be surprised that changes are done.

It is documented in the manual here http://www.amibroker.com/guide/h_categories.html and https://www.amibroker.com/guide/d_ascii.html

Let me quote the manual:

One-click "Update US symbol list and categories"

Automatic setup and update of US stock database is available from Tools->Update US symbol list and categories menu. This is implemented using new #import command and new ASCII importer commands described above.

The command downloads symbol, sector and industry list from amibroker.com web site and create or update current database with stocks listed on NYSE, Nasdaq and AMEX. It also creates sector and industry structure and assigns stocks to proper industries.

CAVEAT: Be aware that using this tool will WIPE (delete) any existing sectors/industries and replace them with the ones imported automatically.

Any symbol that is NOT part of the update list would have its category wiped too because all categories are wiped. As new ones are created in place you will end up with some random category for symbols that are NOT on the update list.

To keep categories untouched you may open Formats\symbolandsec.format file with notepad. You would see two last lines like this:

$CLEANSECTORS 1
$SORTSECTORS 1

You may experiment and remove those two lines so sector structure is kept unchanged, but this will prevent function from removing/renaming old unused sectors and it may prevent from adding new if you run out of free industry slots. If you don't clean exisiting category structure you will end up with new one added to existing one and if your old structure was invalid/wrong it will stay. Cleaning structure allows to remove old wrong entries, but you are free NOT to clean. Do at your own risk. It may not work. You have been warned.

2 Likes

I tried using Update US symbols list and categories. But it would not include the symbol "BMR" and I can find it under https://www.nasdaq.com/market-activity/stocks/bmr.

why is this?

For those who are interested in Yahoo Finance Sector / Industry structure, here is a Excel VBA script that you can use to download by ticker symbol its company name, sector and industry.

VBA script.

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For 32 Bit Systems
#End If

Sub get_market()

Dim XMLpage As New MSXML2.XMLHTTP60
Dim HTMLdoc As New MSHTML.htmlDocument
Dim Elements As IHTMLElementCollection
Dim i As Integer, ttl_row As Integer
Dim array_str As Variant
Dim element As Object

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    ttl_row = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To ttl_row
        Application.StatusBar = "Downloaded: " & ThisWorkbook.Sheets("Sheet1").Cells(i, 1) & "(" & (i - 1) & "of" & (ttl_row - 1) & ")"

        my_url = "https://finance.yahoo.com/quote/" & ThisWorkbook.Sheets("Sheet1").Cells(i, 1) & "/profile"
    
        XMLpage.Open "GET", my_url, False
        XMLpage.send
        HTMLdoc.body.innerHTML = XMLpage.responseText
        While Not XMLpage.readyState = 4    '<---------- wait
            'Application.Wait Now + TimeValue("0:00:01")
            Sleep (500)
        Wend
        
        Set Elements = HTMLdoc.getElementsByTagName("h1")
        If Elements.Length > 0 Then
            ThisWorkbook.Sheets("Sheet1").Cells(i, 2) = Replace(Elements(0).innerText, " (" & ThisWorkbook.Sheets("Sheet1").Cells(i, 1) & ")", "")
        End If
        
        Set Elements = HTMLdoc.getElementsByTagName("span")
        If Elements.Length > 0 Then
            For j = 0 To Elements.Length - 1
                If Elements(j).innerText = "Sector(s)" Then
                    ThisWorkbook.Sheets("Sheet1").Cells(i, 3) = Elements(j + 1).innerText
                End If
                If Elements(j).innerText = "Industry" Then
                    ThisWorkbook.Sheets("Sheet1").Cells(i, 4) = Elements(j + 1).innerText
                End If
            Next j
        End If
        
        Application.Wait (Now + TimeValue("0:00:01"))
    
    Next i
    
    Application.StatusBar = "Download Completed"
    
End Sub

For the above VBA script to work, in the Excel VBA editor, go to Tools -> References and check the following.

  1. Microsoft HTML Object Library
  2. Microsoft XML, V 6.0

In Sheet1 of the Excel spreadsheet, add the following labels in row 1.
column A - Ticker
column B - Name
column C - Sector
column D - Industry

Enter the ticker symbol of interest from row 2 onward of column A.
For debugging purpose, try out a few symbols first.
image

1 Like

I have a python script that gets me all the symbols not from yahoo the list from Amibroker US stocks list doesn't seem complete as indicated above.

I found a solution. I compile my own list then save my CSV as a *.tls file and import it into amiquote to update amibroker DB.