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

Using excel VBA, want to be able to  automate getting historical market data from finance.yahoo.com/q/hp?s=qqqq for example, into a worksheet named qqqq in myWorkbook.xls (one wonders how they managed to have a symbol like qqqq snuck in there;)
The Yahoo! page allows you to specify a from date and a to date and a symbol. It has a link that downloads to a excel.csv file.
I saw an EE solution ID: 22532485 that somehow transfers data from another website to excel. But it looks plenty involved to port it to the finance!yahoo example. There is another solution that looks promising, ID: 21789537 however, it has some reference to a dynamically changing IP address. So in either case I am stymied. Hence the new question.
Regards
Brian
canesbrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Venkateswarlu KaipuCommented:
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
canesbrAuthor Commented:
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
Venkateswarlu KaipuCommented:
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




Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

canesbrAuthor Commented:
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
zorvek (Kevin Jones)ConsultantCommented:
This solution pulls the data from NASDAQ which has a single table that covers up to 10 years. The data is the same except NASDAQ only has the split adjusted prices. If you pass a range to the routine it places the data in that range down and to the right. If you don't pass a range it creates a new sheet and places the table starting in cell A1.

Public Sub Test()

   ImportEquityHistory "qqqq", DateSerial(2006, 6, 1), Now()

End Sub

Public Sub ImportEquityHistory( _
      ByVal Ticker As String, _
      ByVal StartDate As Date, _
      ByVal EndDate As Date, _
      Optional ByVal TargetRange As Range _
   )
   
   Dim DataRows As Range
   Dim Row As Range
   Dim NumberMonths As Long
   
   If TargetRange Is Nothing Then
      Sheets.Add After:=Sheets(Sheets.Count)
      Set TargetRange = ActiveSheet.[A1]
   Else
      Set TargetRange = TargetRange.Cells(1, 1)
   End If
   
   NumberMonths = DateDiff("M", StartDate, EndDate) + 1

   With TargetRange.Parent.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:=TargetRange)
      .Name = "QueryTable"
      .PreserveFormatting = True
      .WebTables = "2"
      .Refresh BackgroundQuery:=False
      Set TargetRange = .ResultRange
      .Delete
   End With
   ActiveSheet.Names("QueryTable").Delete

   Set DataRows = TargetRange.Resize(TargetRange.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

End Sub

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zorvek (Kevin Jones)ConsultantCommented:
Correction: the NASDAQ table is NOT limited to ten years.

Kevin
canesbrAuthor Commented:
Nothing personal, but I love you man!
Regards
Brian
canesbrAuthor Commented:
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
canesbrAuthor Commented:
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
zorvek (Kevin Jones)ConsultantCommented:
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
canesbrAuthor Commented:
Many thanks - All the best,
Regards,
Brian
canesbrAuthor Commented:
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
zorvek (Kevin Jones)ConsultantCommented:
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
canesbrAuthor Commented:
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!)
zorvek (Kevin Jones)ConsultantCommented:
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
canesbrAuthor Commented:
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
zorvek (Kevin Jones)ConsultantCommented:
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
fb1990Commented:
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,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.