从YQL获取股市指数的组成部分
目前，我可以使用 YQL 控制台 返回 xml 和 json 来获取股票报价，例如
选择交易品种、价格来自 csv 其中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'
我想从中获取完整的组件列表
http://download.finance.yahoo.com/d/quotes.csv?s=@^HSI&f=sl1d1t1c1ohgv&e=.csv
使用 YQL 控制台，所以我在 YQL 控制台中输入以下语句
选择符号，价格来自csv 其中url='http://download.finance.yahoo.com/d/quotes.csv?s=@^HSI&f=sl1d1t1c1ohgv&e=.csv' 和 columns='符号，价格，日期，时间，更改， col1,high,low,col2'
但它说我的链接无效。有什么想法和替代解决方案吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您的查询几乎是正确的，但正如您所指出的，该 URL 被视为“无效”。解决方案是正确转义查询字符串值。
变为
仅将这两个字符更改为其 % 编码值允许 YQL 拉回 CSV 数据。
旁白：YQL 不喜欢 CSV 在文件末尾有空行，当您尝试使用
columnswhere 子句时，这会导致问题。如果您可以接受名为col的列，并且想要跳过最后（空）行，请在末尾使用and col8 is not null询问。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.
becomes
Changing just those two characters into their %-encoded values allows YQL to pull back the CSV data.
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
columnswhere clause. If you're okay with having the columns calledcol<number>and want to skip the last (empty) row then useand col8 is not nullat the end of your query.这是正确的网址：
http://quote.yahoo.com/d/quotes.csv?s=&f=sl1d1t1c1ohgv&e=.csv
对于可口可乐：
http://quote.yahoo.com/d/quotes.csv?s=KO&f=sl1d1t1c1ohgv&e=.csv
结果： "KO",69.74,"9/2/2011","4:00pm",-0.71,69.7201,69.99,69.50,8765529
恒指:
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
这是一个 API 文档：
http://www.gummy-stuff.org/Yahoo-data.htm
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
不幸的是，上述解决方案并没有完全回答问题，您只会获得前 51-52 个结果（第一页），而不是完整的索引。
我认为这在原始 YQL 中是不可能的，但您需要编写一些代码来获取 HTML，然后循环遍历组件的每个页面并从中构建您自己的数据表。
我已经尝试了几种方法，并且有一个 C# 脚本可以做到这一点，在 Python 中执行此操作也很简单，只需将其加载到 pandas 数据框中即可，或者如果您想要的只是将其加载到一个简单的列表/元组中用于构建组件列表以测试投资组合的符号。
如果人们仍然对这个解决方案感兴趣，我可以发布它的链接。
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.