使用Yahoo Finance的vba抓取数据 [英] Scraping data with vba from Yahoo finance

问题描述

我需要从Yahoo Finance页面读取股票的收盘价.在使用Google财经页面之前，我已经回答了这个问题，但是该页面不再可用，我相信Google已经完全更改了其财经页面.我相信我可以稍加修改就可以在Yahoo Finance上应用相同的内容.

让我们说Yahoo Finance对股票符号AAPL(苹果)具有以下代码:

 ！[AAPL的Yahooo网页代码] [1] 

我只需要提取值172.77.

这与Google财经页面完美配合.在下面的代码中.

该行:

 "https://finance.google.com/finance?q=" 

替换为:

 "https://finance.yahoo.com/quote/" 

代码在一系列单元格中循环并读取股票代号.我们需要从Yahoo页面获得相同的结果.

 子ImportCurrentPriceNEW()昏暗的appIE作为新的InternetExplorer，html作为HTMLDocument昏暗的item_data作为对象对于k = 6到26步骤1s = 1高= 1L = 1StopLoop = 0q =格式(k，"0")如果IsEmpty(ActiveSheet.Range("$ E $"& q).Value)= True，则使用appIE.Visible = False.navigate"https://finance.google.com/finance?q="&床单(上升趋势Stocks).Range(" $ A $& q).Value直到.readyState = 4:DoEvents:循环执行设置html = .document结束于设置item_data = html.querySelector(.pr span")范围("$ B $"& q).值= item_data.innerText万一下一个appIE.Quit范围("D1").选择结束子 

请让我知道如何修改上面的代码以从Yahoo Finance页面读取数据.

解决方案

也许您不喜欢Google，但我认为您应该考虑使用此小型实用程序.

那应该可以做所有你想做的事，还有一段时间！

否则，在Excel中的数据"选项卡下，单击现有连接"，然后单击"MSN Money Central投资者股票报价".参见下图.输入您的代码/符号，然后单击打开".

如果尚未安装该应用程序，请单击下面的链接，然后按照以下步骤进行设置并在您的计算机上正常工作.

I need to read the closing price of a stock from the Yahoo Finance page. I had this answered before using Google Finance page, but the page is no longer available and I believe Google has completely changed its Finance page. I believe I can apply the same on Yahoo Finance with little modification.

Let s say Yahoo Finance has the following code for the stock symobol AAPL (Apple):

    ![YAHOO WEBPAGE CODE FOR AAPL][1]

I need to only extract the value 172.77.

This was working perfectly with Google Finance page. In my code below.

The line:

    "https://finance.google.com/finance?q="

is replaced by:

    "https://finance.yahoo.com/quote/"

The code loops in a range of cells and reads the stock symbols. We need to get the same results but from the Yahoo page instead.

    Sub ImportCurrentPriceNEW()
    Dim appIE As New InternetExplorer, html As HTMLDocument
    Dim item_data As Object    

    For k = 6 To 26 Step 1
     s = 1
     H = 1
     L = 1
     StopLoop = 0
     q = Format(k, "0")
     If IsEmpty(ActiveSheet.Range("$E$" & q).Value) = True Then  

     With appIE
    .Visible = False
    .navigate "https://finance.google.com/finance?q=" & Sheets("Up Trend 
     Stocks").Range("$A$" & q).Value
    Do Until .readyState = 4: DoEvents: Loop
    Set html = .document
    End With

   Set item_data = html.querySelector(".pr span")
   Range("$B$" & q).Value = item_data.innerText               
   End If    
Next    
appIE.Quit
Range("D1").Select

End Sub

Please let me know how I can modify code above to read data from Yahoo Finance page.

解决方案

Maybe you have an aversion to google, but I think you should consider using this small utility.

http://investexcel.net/multiple-stock-quote-downloader-for-excel/

That should do everything you want and a while lot more!!

Otherwise, in Excel, under the Data tab, click Existing Connections and then click MSN Money Central Investor Stock Quotes. See the image below. Enter your tickers/symbols, and click Open.

If the app is not installed already, click the link below, and follow the steps to get everything setup and working on your machine.

https://appsource.microsoft.com/en-us/product/office/WA104379220?tab=Overview

这篇关于使用Yahoo Finance的vba抓取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！