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.
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
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.
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.
Microsoft HTML Object Library
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.
I found a location where Amibroker can get list of latest US symbols: https://scanner.tradingview.com/america/scan <<< maybe the current method can use this link and parse out the symbols?