Automate getting historical data from Finance.Yahoo website into Excel sheet

Try with the below macro


Sub ImportHistoricalData()
    Dim URL As String
    URL = "http://finance.yahoo.com/q/hp?s=qqqq"
    Sheet1.Activate
    Range("A1").Select

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;" & URL, Destination:=Range("A1"))
        .Name = "hp?s=qqqq_2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "17,20"   '' comment this line if you need to import total data in the webpage
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub



Regards,
Venkat

This is great and all.
However, this picks up a table that is displayed on the page (how do you know that  WebTables should be 17,20? Source?) and doesn't pick up what would appear in the .csv file.
The page that the QueryTables accesses shows First | Prev | Next | Last, so if we are not using the  Download To Spreadsheet, need to loop through from First to Last.
Also need to be able to fill the Start Date and End Date and click button Get Prices before doing anything else.
Regards
Brian

Brian,

It is not possible to loop through the first to last. To import all the data, fisrt you need to copy the URL by clicking on next. remember after clicking the next you have to copy the url and use those urls to import the data.

how do you know that  WebTables should be 17,20? Source?
                 goto DATA -->> Import External Data --> Click on "New Web Query"
It will show a popup in address bar paste "http://finance.yahoo.com/q/hp?s=qqqq"
Click on Go button, The Web page will be populated in that popup. All the table in that webpage will be indicated with  => (yellow color arrows) , Click on that arrows which ever you want to download to excel. Finally Click on Import and select the location  in Excel to where it should be downloaded.
   

Regards,
Venkat

Hmm.. Not very automated. May as well manually click on download to spreadsheet - w/b quicker.
It seems to me that accessing the csv spreadsheet, or something close was achieved in EE 21789537. So I am thinking that this must be possible with a method other than QueryTables??
Regards
Brian

Correction: the NASDAQ table is NOT limited to ten years.

Kevin

Nothing personal, but I love you man!
Regards
Brian

Minor thangs:
(A) Set TargetRange = TargetRange.Cells(1, 1)  gives the value of the contents of cell(1,1) so this need modification?
(B) ActiveSheet.Names("QueryTable").Delete - The second/subsequent time(s) you run the routine (it somehow remembers), or if something happens to prevent reaching this statement, you get names QueryTable_1 QueryTable_2 etc. and the above statement causes an error.
So the following gets them all?
Sub DeleteNamesQueryTable()
    Dim specificName As Name
    For Each specificName In Names
        If specificName.Name Like "*QueryTable*" Then specificName.Delete
    Next specificName
End Sub
(C) You take NumberMonths as StartDate to EndDate but NumberMonths per se is independent of Start or end dates. The site takes from Now() by default, back NumberMonths? So you get the same result, specifying 6 months whether you have parameters 1/1/1975 to 7/1/1975 ot 1/1/2000 to 7/1/2000.
So if this is the case then you don't need the EndDate paramenter, it would always be Now()?
Regards
Brian

Oh, okay, I see what you are doing with Start and End Date - you are deleting rows outside of the range after the extract.
Regards
Brian

That's some pretty good debugging! Now I'm wondering why you asked the question in the first place ;-)

>(A)
The add new query function accepts a single cell and fills the data from there. I am ensuring it is only a single cell because some function will limit the output of you specify more than one cell.

>(B)
I'll have to look at this. I may be using a pre-defined name.

>(C)
You nailed that bug.

>So if this is the case then you don't need the EndDate paramenter, it would always be Now()?
Perhaps. The web page parameters do not allow an end date but I created one in the function anyway. You will notice that I delete all dates outside the boundary. I don't like to limit my customers if I don't have to. So look at as me fixing a deficiency in the NASDAQ query.

Kevin

Many thanks - All the best,
Regards,
Brian

Uhm,....
So, er, as you warned, .... now how do you (un)adjust for splits?
Because the Finance.Yahoo shows an Adjusted Close column but the NASDAQ does not. And the Yahoo!Adjusted column contains the good stuff.
Regards
Brian

Brian,

The correct method for handling splits is to split-adjust your historical data. So if you purchased 100 shares of qqqq for $50 per share prior to the last two-for-one split then you change the purchase entry to 200 shares purchased for $25 per share after the split occurs. This keeps your records aggregatable and you don't have to worry about how historical data is interpreted. Normally, all historical data is split adjusted anyway except in special cases because not doing so makes for some very complex and troublesome work.

If you need your records to reflect the original quantities for the purposes of reconciling old statements or tax records, then maintain the original quantities in the transaction record but don't include them in your aggregation calculations. Once a split occurs after a transaction is recorded, the original quantities become just a notation or comment.

I have tweaked the above routine to be a little more robust. I have not done anything with regard to splits however for the reasons stated above.

Public Sub ImportEquityHistory( _
      ByVal Ticker As String, _
      ByVal StartDate As Date, _
      ByVal EndDate As Date, _
      Optional ByVal TargetRange As Range _
   )

' Import an equity's history from NASDAQ.
'
' Syntax
'
' ImportEquityHistory(Ticker, StartDate, EndDate, [TargetRange])
'
' Ticker - The ticker symbol.
'
' StartDate - The date of the first price information.
'
' EndDate - The date of the last price information.
'
' TargetRange - The range into which to place the imported data. The data is
'   placed starting the top left cell of the provided range. If a single cell
'   is specified then the target range is expanded to fit all the requested
'   data. If mutiple cells are specified then only those cells are used and the
'   rest of the data is discarded. Optional. If omitted then a new worksheet is
'   added and the table placed starting in cell A1 of that worksheet.
   
   Dim CurrentActiveSheet As Object
   Dim DataRows As Range
   Dim Row As Range
   Dim NumberMonths As Long
   Dim ScreenUpdating As Boolean
   Dim DisplayAlerts As Boolean
   Dim ResultRange As Range
   
   Set CurrentActiveSheet = ActiveSheet
   ScreenUpdating = Application.ScreenUpdating
   Application.ScreenUpdating = False
   
   Sheets.Add After:=Sheets(Sheets.Count)
   NumberMonths = DateDiff("M", StartDate, Now()) + 1

   With ActiveSheet.QueryTables.Add( _
      Connection:="URL;http://charting.nasdaq.com/ext/charts.dll?2-1-14-0-0-5" & NumberMonths & "-03NA000000" & Ticker & "-&SF:4|5-WD=484-HT=395--XTBL-", _
      Destination:=ActiveSheet.[A1])
      .Name = "ImportEquityHistoryQueryTable"
      .PreserveFormatting = True
      .WebTables = "2"
      .Refresh BackgroundQuery:=False
      Set ResultRange = .ResultRange
      .Delete
   End With
   ActiveSheet.Names("ImportEquityHistoryQueryTable").Delete

   Set DataRows = ResultRange.Resize(ResultRange.Rows.Count - 1).Offset(1)
   For Each Row In DataRows.Rows
      If Row.Cells(1, 1) < StartDate Or Row.Cells(1, 1) > EndDate Then Row.ClearContents
   Next Row
   DataRows.Sort DataRows.Columns(1), xlDescending
   
   If Not TargetRange Is Nothing Then
      If TargetRange.Cells.Count > 1 Then
         TargetRange.Value = ResultRange.Resize(TargetRange.Rows.Count, TargetRange.Columns.Count).Value
      Else
         TargetRange.Resize(ResultRange.Rows.Count, ResultRange.Columns.Count).Value = ResultRange.Value
      End If
      DisplayAlerts = Application.DisplayAlerts
      Application.DisplayAlerts = False
      ActiveSheet.Delete
      Application.DisplayAlerts = DisplayAlerts
   End If
   
   CurrentActiveSheet.Activate
   Application.ScreenUpdating = ScreenUpdating

End Sub

Kevin

Many thanks Kevin
I compared Finance.Yahoo.com and Nasdaq.com Oracle's historicals and show a specific date 1/30/1990 below. (I chose ORCL because it rhymes..)
It appears that Yahoo's history (open, high, low close) is unadjusted for splits and dividends and shows an adjusted for splits Closing price. Nasdaq's history is all adjusted for splits and dividends etc. In addition Nasdaq's adjusted numbers have more needed precision - Yahoo rounds to 2 places.
It is unclear what either/neither/both have for Volume - adjusted or unadjusted.
Yahoo also seems to go further back than Nasdaq.
Regards
Brian

Ticker      ORCL      
            
Oldest Nasdaq      1/2/1990      
Oldest Yahoo!      3/2/1988      
            
Date      1/30/1990      
Yahoo!Open      21      Unadjusted for splits - price on the day
Yahoo!High      21.12      Unadjusted for splits - price on the day
Yahoo!Low      19.87      Unadjusted for splits - price on the day
Yahoo!Close      20.5      Unadjusted for splits - price on the day
Yahoo!Volume      44327200      ??? Adjusted/Unadjusted volume
Yahoo!Adj Close      0.51      Adjusted for splits
            
Open NASD      0.506172      Adjusted for splits
High NASD      0.521604      Adjusted for splits
Low NASD      0.49074      Adjusted for splits
Close/Last NASD      0.506172      Adjusted for splits
Volume NASD      44262554      ??? Adjusted/Unadjusted volume
            
Adjusted Delta      0.003828      NASD has more precision. (Needed!)

I was unable to find a limit to how far back NASDAQ goes. While the web page only displays a maximum of 10 years I was able to get the query for table to go back further. Did you actually find a hard limit?

Kevin

Hey Kevin,
I haven't looked that 'hard' for a hard limit. I requested a start date of 1/1/1980 and Yahoo provided from 1988 and Nasdaq from 1990.
Do you have any observations about the Volume? Adjusted? Unadjusted? Who cares?
Regards
Brian

Nope. I'm not that much of an investor to get that much into the details. The times when I have done work for the big boys we always use a system like Bloomberg - and pay for it.

Kevin

Hello Kevin,

I have problem that is similar to the one that you helped solved.  How can i can get you to help out?  I am login in to a web site and pulling data using their web api.  

Please let know if you can provide assitance.

Thanks,