AJAX Stock Symbol Drop-down List

• Download StockPicker.zip - 319.5 KB

Introduction

This ASP.NET user control can be used to assist users in selecting/picking a valid stock code from a drop-down list without effecting the webpage performance.

Users just enter the first letters of stock symbol, then the list of stock symbols with the fisrt letters will show in drop-down list. Accordingly, users can select the limited list of symbols from drop-down list.

Background

Recently, there are more than 10,000 stock symbols and it will effect the MS SQL and ASP page performance if all stock symbols are loaded and displayed in drop-down list. Users also find it diffcult to locate a stock symbol from a huge drop-down list. So, a limited list of symbols should be showed in accordance to the symbols' first letters typed by users.

Using the code

Run sample:

After downloading, unzip the StockPicker.zip to a folder"

1) Create a Database i.e BlueVision in in SQL Server Management Studio

2) Run script \App_Data\populatedata.sql in SQL Server Management Studio

3) Change the web.config

<add name="LocalSqlServer" connectionString="Data Source=(local);Integrated Security=True; database=BlueVision"/>

4) Create a Virtual Directory: StockPicker pointing to the StockPicker Folder

5) Open browser type: http://localhost/StockPicker/default.aspx

Reuse UserControls:

1. Create the database and load data as mentioned above.

2. Copy the UserControls folder to your website

2. Add the line below to the aspx page which you want to show the stockpicker control

<%@ REGISTER SRC="~/UserControls/StockPicker.ascx" TAGNAME="StockPicker" TAGPREFIX="Evisional" %>

3. Add the line below to the place you want to display the control

4. To get the selected value/symbol in the the submit event, you can use:

StockPicker1.ExchangeID : ExchangeID <br />StockPicker1.StockCode : Stock Code/Symbol <br />StockPicker1.StockID : Stock ID in the database 

Points of Interest

You can notify that the stock symbols are filtered based on the user-typed symbols' first letters. Insteads of using ASP.NET AJAX technique, I use iframe (named stockList) to refresh the stock symbols.

When the onKeyUp events of _StockCode textbox and _ExchangeId dropdown list is triggered, the src property of stockList iframe will be change as follows:

1) Iframe control in StockPicker.ascx:

<ASP:HIDDENFIELD ID=_StockId RUNAT="server" />
<table>
<tr>
<td>
<ASP:DROPDOWNLIST id=_ExchangeId RUNAT="server" DATASOURCEID="SQLExchangeSource"
DATATEXTFIELD="ExchangeName" DATAVALUEFIELD="ExchangeID">
</ASP:DROPDOWNLIST>
<td>
<ASP:TEXTBOX id=_StockCode width=100px RUNAT="server" /><br>
<span style="position:absolute">
<iframe id="_StockList" frameborder="0" scrolling=auto src="<%=request.ApplicationPath%>/UserControls/StockPicker.aspx" 
style="position:relative;top:0; left:0; width:400px; height:150px; background: #ffffff; display:none; border: 1px solid #222222"></iframe>
</span>
</td>
</tr>
</table>

function onKeyUp() { 
       
    var exchangeID = document.getElementById('<%=_ExchangeId.ClientID %>').value;
    var stockCode = document.getElementById('<%=_StockCode.ClientID %>').value;
    var stockList = document.getElementById('_StockList');
                 
  if (stockCode.value!='NaN') { 
     stockList.src='http://www.yourdomain.com/UserControls/StockPicker.aspx?t=1&ExchangeId='+
     exchangeID+'&StockCode='+stockCode; stockList.style.display='block';  
  } 
   else stockList.style.display='none'; 
 
} 

3) In StockPicker.ascx.vb onload event, assign onKeyUp event for _StockCode and _ExchangeId

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
   
    SQLExchangeSource.SelectCommand = "SELECT * FROM StockExchange"
    _ExchangeId.DataBind()
    _ExchangeId.SelectedValue = 1

    _StockCode.Attributes("onkeyup") = "onKeyUp();"
    _ExchangeId.Attributes("onchange") = "onKeyUp();"


End Sub

Listed Companies/Stock Code List

For people who want to know where I get the stock code list: I searched on Google with keywords: "listed companies" and found :

US stock list: http://www.nasdaq.com/reference/comlookup.stm#viewdownload

Australian Stock List: http://www.afrsmartinvestor.com.au/tables.aspx
or http://www.asx.com.au/asx/downloadCsv/ASXListedCompanies.csv

UK: http://www.londonstockexchange.com/en-gb/pricesnews/statistics/listcompanies/

SWISS: http://www.swx.com/admission/listing/equity_market/download/issuers_equity_market_en.csv

If anyone knows other lists, please put a comment. I will update this list.