14
$\begingroup$

I am doing some manipulation of historical stock prices on the NYSE. I want to go through each stock.

To find the number of entities on NYSE, one can use:

Length[FinancialData["NYSE", "Members"]]

It returns 5431. However, the number of stocks on NYSE are ~2500. The above call to FinancialData returns indices as well as stocks (and other things?). Removing the indices:

Length[Select[FinancialData["NYSE", "Members"], (!StringMatchQ[#, ___ ~~ "^" ~~ ___] &)]];

returns 4887, which is still about 2000 more than the number of stocks. When I try to process all the returned entities, and filter away entities for which there are errors:

test = Quiet[
   Check[
    returnData = FinancialData[name, "Close", startDate],
    False]
  ];

If[test == False || returnData == Missing["NotAvailable"],
  Return[{}], 
  0
 ];

3242 remain. Still 700 too many, and that's assuming I could get data for all stocks.

My question: What is really returned by FinancialData["NYSE", "Members"]? Does it include things such as futures or funds? How do I select stocks only?

And what are the ~1600 entities that are "NotAvailable" or give an error? (Or don't have the same Length[] as the corresponding index ^NYA, which is another thing I check.)

$\endgroup$
  • $\begingroup$ There are 29 pages of 100 stocks each currently on the NYSE site. So I guess the number of stocks is closer to 2900. Perhaps the other members are historic stocks? $\endgroup$ – Sjoerd C. de Vries Dec 16 '15 at 14:46
6
$\begingroup$

What is included in FinancialData["NYSE", "Members"] appear to be stocks, indices, and exchange traded funds (ETFs). One can see this by finding the intersection of the group with the various "Classes" of FinancialData:

nyse = FinancialData["NYSE", "Members"];

{#, Length@ Intersection[FinancialData@#, nyse]} & /@ FinancialData@"Classes"
{{"Currencies", 0}, {"Exchanges", 0}, {"ExchangeTradedFunds", 2}, 
 {"Futures", 0}, {"Indices", 544}, {"MutualFunds", 0}, {"Sectors", 0}, {"Stocks", 4885}}

We can select out the stocks:

nysestocks = Intersection[nyse, FinancialData@"Stocks"];

but, of course, there are still too many (4885), at least according to the naïve counting of the NYSE listings. Most of the excess, it turns out, are ETFs, which is not surprising as FinancialData only identifies 2 as on the NYSE, and there are many. A quick check can confirm this:

Select[
  FinancialData["NYSE", "Members"], 
  StringContainsQ[FinancialData[#, "Name"], "etf", IgnoreCase -> True] &] // Length
268

This is still to small to account for everything (one can't expect "ETF" to be in the name of every ETF), but an indication that the ETFs are in there.

Filtering the ETFs and indices out of nysestocks is probably not possible with only the tools of FinancialData. In its current state, it doesn't appear to provide enough information to allow ETFs to be reliably distinguished from stocks (yes, one can make some progress by filtering based on "Name", or perhaps "Volume", but there isn't any surefire way to determine security type...probably they are mixed in with the stocks only by mistake, so it is not surprising that the tools to extract them are lacking), although perhaps someone else may have some suggestions.


The following is a partial answer to the second part of your question. There are, without a doubt, other and better ways to do this.

Mathematica can still help us here, provided we look elsewhere for our data. Yahoo! Finance and Google Finance both provide access to a wealth of financial information. A little research shows that both also can be queried programmatically (this and this were helpful):

YahooFinanceLookup[symbol_String] := Quiet@With[
  {sym = Last@StringSplit[symbol, ":"]},
  "type" /. Flatten@Cases[
     Import["http://finance.yahoo.com/webservice/v1/symbols/" <> sym <> "/quote?format=json", "JSON"],
     {___, "symbol" -> _, ___}, Infinity]
 ] /. "type" -> $Failed

GoogleFinanceLookup[symbol_String] := Quiet@With[
   {rule = ("\"" <> ("e" | "type") <> "\" : \"" ~~ s : (Except["\"", _] ..) ~~ "\"") :> s},
   Import[
     "http://www.google.com/finance/info?infotype=infoquoteall&q=" <> symbol, "String"
    ] /. str_String :> StringCases[str, rule]
  ]

YahooFinanceLookup returns what Yahoo! Finance considers the "type" of security symbol, which is either "equity" or "etf".

GoogleFinanceLookup returns the name of the exchange on which the security symbol is listed and what Google Finance considers the "type", which is usually "Company" if it is known, and which unfortunately is applied to both stocks and ETFs.

We can apply these to filter out some of the ETFs:

yahoostocks = GroupBy[{#, YahooFinanceLookup@#} & /@ nysestocks, Last];

Length /@ yahoostocks
<|"equity" -> 2467, "etf" -> 1086, $Failed -> 1332|>

The "equity" elements I believe are stocks (I don't know exactly how it is defined in this context, though). The $Failed elements are the securities that Yahoo! didn't know. We can also try with Google:

googlestocks = GroupBy[{#, GoogleFinanceLookup@#} & /@ nysestocks, Last]; 

Length /@ googlestocks
<|{"NYSE", "Company"} -> 2414, {"NYSEARCA", "Company"} -> 1162, 
  $Failed -> 1072, {"OTCMKTS", "Company"} -> 81, 
  {"NASDAQ", "Company"} -> 105, {"NYSEMKT", "Company"} -> 33, 
  {"BATS", "Company"} -> 4, {"Company"} -> 11, 
  {"INDEXDJX", "Company"} -> 2, {"OTCBB", "Company"} -> 1|>

Well, there appear to be some securities from the NASDAQ, Dow Jones, and other exchanges listed as on the NYSE. It seems that the curated FinancialData might be significantly out of date in some areas.

If we gather together all of the entries that we think are stocks and that we know are on the NYSE, we get

NYSEstocks = Intersection[
   (yahoostocks["equity"])[[All, 1]], 
   (googlestocks[{"NYSE", "Company"}])[[All, 1]]
  ];

Length@ NYSEstocks
2140

This is less than it should be, but the entries should all be NYSE stocks. Some of the elements that came up as $Failed according to either YahooFinanceLookup or GoogleFinanceLookup are stocks that should be included in this list, but at the moment I have exhausted my research energies, so I will leave which ones those are as an open question.


As an additional resource, I can point you to a list of NYSE companies maintained by NASDAQ, which can be downloaded in CSV format. That list has 3245 entries, but does not distinguish type of security. The security symbol naming convention is also slightly different, which inhibits facility of comparison.

$\endgroup$
  • 1
    $\begingroup$ Great answer, it's a shame that FinancialData and other financial data functions have less coverage than before. I always default to the Quandl API nowadays - community.wolfram.com/groups/-/m/t/604692 $\endgroup$ – Martin John Hadley Jan 19 '16 at 8:58
  • $\begingroup$ @MartinJohnHadley Quand1 looks quite interesting. When I get the time I will take a good look. Thanks! $\endgroup$ – Virgil Jan 19 '16 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.