3

I want to write a SPARQL query that gives me the wikidata_id, label, stock exchange, and ticker symbol for all instances of a company being listed on a stock exchange.

My query so far looks like

SELECT DISTINCT ?id ?idLabel ?exchangeLabel ?tickerLabel
WHERE {
  ?id wdt:P31/wdt:P279* wd:Q783794 ;
      wdt:P414 ?exchange ;
      p:P414 [pq:P249 ?ticker].
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

While this produces results that almost seem right, there is a problem when companies are listed on multiple exchanges -- here's an example of the problem in the results: enter image description here Note how in the above, Credit Suisse is listed three times, with three different tickers. While it's correct that Credit Suisse is listed on three stock exchanges, the problem is that the NYSE is listed as the exchange in all three cases. Even worse, there are in fact nine rows for Credit Suisse, associating every listing with every stock exchange. The correct listing info would contain only three listings, and is provided on Credit Suisse's wikidata page: enter image description here

What am I doing wrong? How can I get the correct exchange to be associated with each ticker's row?

conradlee
  • 12,985
  • 17
  • 57
  • 93
  • 2
    `?id wdt:P31/wdt:P279* wd:Q783794 ; p:P414 [pq:P249 ?ticker; ps:P414 ?exchange ] .` And you current query returns 9 results for `Q372657`, not 3. – Stanislav Kralin Feb 05 '19 at 14:08
  • 1
    Ok, he was 2s faster. The answer in textual form in addition to the query pattern is, you have to refer to the same statement and get the value out of it, otherwise you'll just get what you got, the combinations – UninformedUser Feb 05 '19 at 14:10
  • thanks @StanislavKralin if you write it up as an answer I will accept it--if you don't, then I'll write it up as an answer and give your comment credit. – conradlee Feb 05 '19 at 14:16
  • @conradlee OK, please write the answer yourself! – Stanislav Kralin Feb 05 '19 at 14:21

1 Answers1

3

Thanks to @StansilavKralin (in a comment to my question) I can provide an answer:

SELECT DISTINCT ?id ?idLabel ?exchangeLabel ?tickerLabel
WHERE {
  ?id wdt:P31/wdt:P279* wd:Q783794 ; p:P414 [pq:P249 ?ticker; ps:P414 ?exchange ] .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
conradlee
  • 12,985
  • 17
  • 57
  • 93