2

Currently, I can get stock quote by returning xml and json using YQL console like

select symbol, price from csv where url='http://download.finance.yahoo.com/d/quotes.csv?s=IBM,YHOO,GOOG,MSFT&f=sl1d1t1c1ohgv&e=.csv' and columns='symbol,price,date,time,change,col1,high,low,col2'

I would like to get the complete component list from

http://download.finance.yahoo.com/d/quotes.csv?s=@^HSI&f=sl1d1t1c1ohgv&e=.csv

using YQL console, so I input the statement below in YQL console

select symbol, price from csv where url='http://download.finance.yahoo.com/d/quotes.csv?s=@^HSI&f=sl1d1t1c1ohgv&e=.csv' and columns='symbol,price,date,time,change,col1,high,low,col2'

but it said my link is invalid. Any thoughts and alterative solutions?

morrison
  • 43
  • 1
  • 3

3 Answers3

3

Your query was very nearly correct, but the URL was considered "invalid" as you noted. The solution is to properly escape the query string values.

http://download.finance.yahoo.com/d/quotes.csv?s=@^HSI&f=sl1d1t1c1ohgv&e=.csv

becomes

http://download.finance.yahoo.com/d/quotes.csv?s=%40%5EHSI&f=sl1d1t1c1ohgv&e=.csv

Changing just those two characters into their %-encoded values allows YQL to pull back the CSV data.

select * from csv where url='http://download.finance.yahoo.com/d/quotes.csv?s=%40%5EHSI&f=sl1d1t1c1ohgv&e=.csv'

Aside: YQL doesn't like that the CSV has an empty line at the end of the file, this will cause issues when you try to use the columns where clause. If you're okay with having the columns called col<number> and want to skip the last (empty) row then use and col8 is not null at the end of your query.

salathe
  • 51,324
  • 12
  • 104
  • 132
  • That exactly want I want. Although I have done an URL encoding in my application, I need to encode the escape character **first** and then encode the whole query again. More, I missed the character **@**. Can't find it in the document. Thank you very much!!! – morrison Sep 08 '11 at 04:35
  • Unfortunately, this type of URL ```http://download.finance.yahoo.com/d/quotes.csv?s=%40%5EHSI&f=sl1d1t1c1ohgv&e=.csv``` will no longer return a useable result, since Yahoo changed the interface. – user2161065 Sep 28 '16 at 16:47
0

The above solutions don't fully answer the question unfortunately, you will only get the first 51-52 results (the first page) and not the full index.

I don't think this is possible in raw YQL but you will need to write some code to get the HTML and then loop through each page of components and build your own datatable up from it.

I have tried a few ways and have a C# script that can do it, it would also be trivial to do this in Python and just load it into a pandas dataframe as you go along or just a plain list/tuple if all you want is the symbols to build up a component list for testing a portfolio against.

If people are still interested in this solution I can post the link to it.

MJB
  • 101
  • 1
  • 5
0

This is the correct url:

http://quote.yahoo.com/d/quotes.csv?s=<symbol>&f=sl1d1t1c1ohgv&e=.csv

For Coca Cola:

http://quote.yahoo.com/d/quotes.csv?s=KO&f=sl1d1t1c1ohgv&e=.csv

Result: "KO",69.74,"9/2/2011","4:00pm",-0.71,69.7201,69.99,69.50,8765529

For HSI:

http://quote.yahoo.com/d/quotes.csv?s=^HSI&f=sl1d1t1c1ohgv&e=.csv

"^HSI",19616.40,"9/5/2011","4:01am",-596.51,19830.50,19830.50,19567.77,0

Here is an API document:

http://www.gummy-stuff.org/Yahoo-data.htm

bigspotteddog
  • 1,449
  • 13
  • 14
  • Thank you for answering, but I would like to have the component list since HSI is an stock market index. In your answer the symbol HSI is simply dummy value so you can see there are several field with "N/A". – morrison Sep 05 '11 at 08:26
  • I corrected the symbol for HSI to ^HSI, since it is an index. The url I provided is the correct url, but you need to make sure you use the right symbol by checking finance.yahoo.com to see what they have assigned to it. – bigspotteddog Sep 05 '11 at 15:59
  • Yes, but firstly this url is getting the data of ^hsi itself but not the component. Secondly, I cannot use this link with statement in YQL console like: select symbol, price from csv where url='[http://quote.yahoo.com/d/quotes.csv?s=^HSI&f=sl1d1t1c1ohgv&e=.csv](http://quote.yahoo.com/d/quotes.csv?s=^HSI&f=sl1d1t1c1ohgv&e=.csv') and columns='symbol,price,date,time,change,col1,high,low,col2'. Anyway, thank you. – morrison Sep 06 '11 at 01:53