Getting Stock symbol from CUSIP or company name

Author: bobydesi123

Creation Date: 8/26/2013 9:07 AM

Wealth Lab » WealthScript (.NET) » Getting Stock symbol from CUSIP or company name

bobydesi123

8/26/2013 9:07 AM

Problem:
I am trying to extract stock symbols from the SEC FORM 13-F data to WLP dataset or even a text file. The Form 13-f lists the stocks by CUSIP and the company name but not the the stock symbols.
http://www.sec.gov/Archives/edgar/data/898382/000114036113032356/xslForm13F_X01/form13fInfoTable.xml

Question:
Does any one know a way to get stock symbol name from a given CUSIP or the the Company name.
I am looking an automated way to do it , may be WL can help? WL symbol object does not seem to have CUSIP as a property.
I know i can do it individually using tools available on web buy that is too cumbersome. Using company name to get stock symbol can result in ambiguity, e.g. APOLLO INVESTMENT CORP would result in three different stock symbols, AINV AIB AIY on yahoo. CUSIP will result in AINV.

Thanks in advance.
.

bobydesi123

8/26/2013 12:36 PM

Seems like all the WL Bars.SecurityName have ',INC' attached to it. I think if i strip the ',INC' and use the InStr(Bars.SecurityName, SEC's company name), it should mostly work. However, if some one does have solution idea with CUSIP, i would appreciate it.
Thanks.

Eugene

8/26/2013 12:42 PM

UPDATE 08/17/2017: Fidelity URL change

How about this?

This solution will download your SEC form, parse it, extract all CUSIPs, then look up by the symbol name of each one using Fidelity's tool. Uses HtmlAgilityPack (available with latest Community.Components), requires WL 6.4 or greater.

Prerequisites:

1. Installed Community Components 2012.12 or higher - just because it includes the required HtmlAgilityPack.dll v1.4.6
2. In Strategy Editor, click "References...", scroll down and check System.Xml
3. Until the Editor gets upgraded in WL 6.? to support C#4.0 out of the box, you need to add a reference to System.Core v4.0. Switch to "Other assemblies to reference...", click "Add a reference", find the file called System.Core.dll, highlight it and confirm. The file's placement depends on your OS and WLP "bitness". For 64-bit WLP, choose c:\windows\microsoft.net\framework64\v4.0.30319, for 32-bit WLP it's c:\windows\microsoft.net\framework\v4.0.30319

Give it a minute or two to run - after all, it's processing ~80 requests synchronously:

CODE:
Please log in to see this code.

Size:

Color:

[color=#f700ff][b]UPDATE 08/17/2017: Fidelity URL change[/b][/color]

How about this?

This solution will download your SEC form, parse it, extract all CUSIPs, then look up by the symbol name of each one using Fidelity's tool. Uses HtmlAgilityPack (available with latest Community.Components), requires WL 6.4 or greater.

[b]Prerequisites[/b]:

1. Installed [link=http://www.wealth-lab.com/Extensions/Details/10]Community Components[/link] [b]2012.12[/b] or higher - just because it includes the required [b]HtmlAgilityPack.dll[/b] v1.4.6
2. In Strategy Editor, click "References...", scroll down and check [b]System.Xml[/b]
3. Until the Editor gets upgraded in WL 6.? to support C#4.0 out of the box, you need to add a reference to System.Core v4.0. Switch to "Other assemblies to reference...", click "Add a reference", find the file called [b]System.Core.dll[/b], highlight it and confirm. The file's placement depends on your OS and WLP "bitness". For 64-bit WLP, choose [color=#fa00d0]c:\windows\microsoft.net\framework[b]64[/b]\v4.0.30319[/color], for 32-bit WLP it's [color=#fa00d0]c:\windows\microsoft.net\framework\v4.0.30319[/color]

Give it a minute or two to run - after all, it's processing ~80 requests synchronously:

[code]using System;
using System.Collections.Generic;
using System.Text;
using System.Drawing;
using WealthLab;
using System.Linq;
using System.IO;
using System.Net;
using HtmlAgilityPack;

namespace WealthLab.Strategies
{
	public class MyStrategy : WealthScript
	{
		public List<string> GetListOfCusipsFromSEC()
		{
			var lstCUSIP = new List<string>();
			string URI = @"http://www.sec.gov/Archives/edgar/data/898382/000114036113032356/xslForm13F_X01/form13fInfoTable.xml";
			HtmlWeb web = new HtmlWeb();
			HtmlDocument doc = web.Load(URI);
			
			if (doc.DocumentNode != null)
			{
				try {	        
					var rawItems = from row in doc.DocumentNode.SelectNodes("//table[2]/tbody/tr").Cast<HtmlNode>()
							select new { Row = row.Descendants("td").ToArray() };
					var rows = rawItems.ToArray();
					
					foreach( var r in rows )
					{
						if( r.Row.Count() >= 12 )
						{
							string cusip = r.Row[2].InnerText;
							if( !cusip.StartsWith( "CUSIP" ) && !string.IsNullOrEmpty(cusip) )
								lstCUSIP.Add( cusip );
						}
					}
				}
				catch (Exception) {		
					throw;
				}
			}
			
			doc = null;
			return lstCUSIP;
		}

public string GetSymbolByCUSIP( string CUSIP )
		{
			string s = string.Empty;
			string xpathToSym = "//font";
			string url = string.Format( 
				//"http://activequote.fidelity.com/mmnet/SymLookup.phtml?reqforlookup=REQUESTFORLOOKUP&productid=mmnet&isLoggedIn=mmnet&rows=50&for=stock&by=cusip&criteria={0}&submit=Search", CUSIP );
				"http://quotes.fidelity.com/mmnet/SymLookup.phtml?reqforlookup=REQUESTFORLOOKUP&productid=mmnet&isLoggedIn=mmnet&rows=50&for=stock&by=cusip&criteria={0}&submit=Search", CUSIP );
			WebClient webClient = new WebClient();
			string raw = webClient.DownloadString(url);

HtmlDocument doc = new HtmlDocument();
			doc.OptionFixNestedTags = true;
			doc.LoadHtml(raw);
			
			if (doc.DocumentNode != null) {
				try {
					HtmlNodeCollection nc = doc.DocumentNode.SelectNodes(xpathToSym);
					if (nc != null) {
						if( nc.Count >= 5 ) {
							s = WebUtility.HtmlDecode( nc[5].InnerText );
						}
					}
				}
				catch (Exception) {		
					throw;
				}				
			}

doc = null;			
			return s;
		}
		
		protected override void Execute()
		{
			PrintDebug( "CUSIP" + "\t" + "Symbol" + "\r\n" );
			foreach( var s in GetListOfCusipsFromSEC() )
			{				
				PrintDebug ( s + "\t" + GetSymbolByCUSIP( s ) );
				//PrintDebug ( s );
			}
		}
	}
}[/code]

bobydesi123

8/26/2013 1:00 PM

Super! Worked like a charm! Thanks as always have a nice day.

bobydesi123

8/26/2013 3:18 PM

QUOTE:
Give it a minute or two to run - after all, it's processing ~80 requests synchronously:

Surprisingly, it did not take more than 5-6 seconds!
BTW, if you happen to read this comment, is WL6.5..making use of multiple CPUs? I remember there being some discussion about it in one of the threads.