Solved

Importing Data from Yahoo Finance into Excel

Posted on 2012-04-03
3
505 Views
Last Modified: 2012-04-04
I am looking to import historical stock market data from yahoo finance into excel via the import from web function. The problem I face is that yahoo splits the results into a series of pages and only the first page of data is actually imported into excel due to the fact that the date range is large. Does anyone know how you can import the full table of data in one go?
0
Comment
Question by:draeside
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 6

Accepted Solution

by:
wshark83 earned 300 total points
ID: 37801710
how about a simple vba code:

just insert start date, end date and stock symbol:


Sub GetData()

    Dim DataSheet As Worksheet
    Dim EndDate As Date
    Dim StartDate As Date
    Dim Symbol As String
    Dim qurl As String
    Dim nQuery As Name
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
   
    Set DataSheet = ActiveSheet
 
        StartDate = DataSheet.Range("B1").Value
        EndDate = DataSheet.Range("B2").Value
        Symbol = DataSheet.Range("B3").Value
        Range("C7").CurrentRegion.ClearContents
       
        qurl = "http://ichart.yahoo.com/table.csv?s=" & Symbol
        qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
            "&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
            Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & Range("E3") & "&q=q&y=0&z=" & _
            Symbol & "&x=.csv"
        Range("b5") = qurl
                   
QueryQuote:
             With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With
           
            Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, other:=False
           
            Range(Range("C7"), Range("C7").End(xlDown)).NumberFormat = "mmm d/yy"
            Range(Range("D7"), Range("G7").End(xlDown)).NumberFormat = "0.00"
            Range(Range("H7"), Range("H7").End(xlDown)).NumberFormat = "0,000"
            Range(Range("I7"), Range("I7").End(xlDown)).NumberFormat = "0.00"

End Sub
0
 

Author Comment

by:draeside
ID: 37802529
Thanks  wshark83. Unfortunately I am not a VB programmer. Would you be able to put that into a spreadsheet and upload it for me?
0
 
LVL 6

Expert Comment

by:wshark83
ID: 37805290
here you go, I've added an example. remember you need to insert the symbol as it is in yahoo.
YahooStockData.xls
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question