+ Reply to Thread
Results 1 to 14 of 14

Pull Yahoo Multiple stock data (Old version code not working)

  1. #1
    Registered User
    Join Date
    08-07-2012
    Location
    India
    MS-Off Ver
    Microsoft office home and student 2016 (Windows 10 Home 64bit)
    Posts
    98

    Pull Yahoo Multiple stock data (Old version code not working)

    Hello Everyone,

    Please check BELOW VBA CODE. Really great work. Earlier i used to download multiple stock data through this file but now file code not working yahoo changed there links.
    Someone please help me to work this file AND THIS IS MY FIRST THREAD SO EXCELFORUM NOT ALLOWING ME TO INSERT ANY LINK OR URL so have just mentioned in bold with colour "YAHOO OLD LINK"

    Thanks you very much in advance




    Sub GetData()
    ' thanks to Ron McEwan :^)

    Dim QuerySheet As Worksheet
    Dim DataSheet As Worksheet
    Dim EndDate As Date
    Dim StartDate As Date
    Dim Symbol As String
    Dim qurl As String
    Dim nQuery As Name
    Dim i As Integer, N As Integer, pct As Double


    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationAutomatic

    Set DataSheet = ActiveSheet

    N = Range("C1")

    Clear ' clear old data
    Init ' paste headings
    ' ----------------------------------


    For i = 1 To N

    Range("A1") = i
    Range("B4") = Cells(i + 7, 1) ' get symbol
    Cells(i + 7, 1).Select

    GetOne ' download one stock
    UpdateScale ' update chart scale

    Application.ScreenUpdating = False

    Range("K5:AE5").Select ' collect calculations
    Selection.Copy

    Sheets("Calculations").Select ' move to Calculations sheet
    Cells(i + 7, 3).Select ' select proper row and paste calculations
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Sheets("Download").Select ' move to Data sheet

    Application.ScreenUpdating = True

    Next i

    Range("A1").Select
    Selection.ClearContents

    Sheets("Calculations").Select
    GetNames
    Formats
    Range("C1").Select

    End Sub

    Sub GetOne()
    ' Download one stock only

    Dim QuerySheet As Worksheet
    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 = True
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationAutomatic

    Set DataSheet = ActiveSheet

    StartDate = DataSheet.Range("B2").Value
    EndDate = DataSheet.Range("B3").Value
    Symbol = DataSheet.Range("B4").Value
    Range("C7").CurrentRegion.ClearContents

    'construct the URL for the query

    qurl = "YAHOO OLD LINK" & Symbol
    qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
    "&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
    Day(EndDate) & "&f=" & Year(EndDate) & "&g=d" & "&q=q&y=0&z=" & _
    Symbol & "&x=.csv"

    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("C1:I1").Select
    Selection.ColumnWidth = 8

    'turn calculation back on

    Application.DisplayAlerts = True
    Range("C8:I600").Select
    Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    RemoveNames

    Range("A1").Select

    End Sub
    Sub RemoveNames()

    Dim nQuery As Name

    For Each nQuery In Names
    If IsNumeric(Right(nQuery.Name, 1)) Then
    nQuery.Delete
    End If
    Next nQuery

    End Sub

    Sub Clear()
    '
    ' Clear Macro
    ' Macro recorded 8/13/2006 by Ponzo

    Sheets("Calculations").Select ' move to Calculations sheet

    Range("A8:AE600").Select ' CLEAR OLD STUFF
    Selection.ClearContents
    Formats
    Sheets("Download").Select ' move back

    End Sub
    Sub Move()
    '
    ' Move Macro
    ' Macro recorded 09/03/2007 by pjPonzo
    '

    Range("C7:C600").Select
    Selection.Copy
    Range("K7").Select
    ActiveSheet.Paste

    Range("I7:I600").Select
    Selection.Copy
    Range("L7").Select
    ActiveSheet.Paste

    End Sub
    Sub Init()
    '
    ' Init Macro
    ' Macro recorded 09/03/2007 by pjPonzo
    '

    '
    Range("K3:AE4").Select
    Selection.Copy
    Sheets("Calculations").Select
    Range("C5").Select
    ActiveSheet.Paste
    Sheets("Download").Select
    End Sub

    Sub GetNames()

    Dim QuerySheet As Worksheet
    Dim DataSheet As Worksheet
    Dim qurl As String
    Dim i As Integer

    Sheets("Calculations").Select

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual

    Set DataSheet = ActiveSheet

    Range("A8").CurrentRegion.ClearContents
    i = 8
    qurl = "YAHOO OLD LINK" + Cells(i, 3)
    i = i + 1
    While Cells(i, 3) <> "" And i < 200
    qurl = qurl + "+" + Cells(i, 3)
    i = i + 1
    Wend
    qurl = qurl + "&f=n"
    ' Range("c1") = qurl
    QueryQuote:
    With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("A8"))
    .BackgroundQuery = True
    .TablesOnlyFromHTML = False
    .Refresh BackgroundQuery:=False
    .SaveData = True
    End With

    Range("A8").CurrentRegion.TextToColumns Destination:=Range("A8"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=True, Space:=False, other:=False


    'turn calculation back on
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True

    Columns("A:A").Select
    Selection.ColumnWidth = 20

    End Sub

    Sub Formats()
    '
    ' Formats Macro
    ' Macro recorded 9/5/2008 by pjPonzo
    '

    '
    Range("D8:Z8").Select
    Selection.Copy
    Range("D9:Z600").Select
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

    Range("A1").Select
    Selection.ClearContents

    End Sub

  2. #2
    Registered User
    Join Date
    08-07-2012
    Location
    India
    MS-Off Ver
    Microsoft office home and student 2016 (Windows 10 Home 64bit)
    Posts
    98

    Extract multiple data from one sheet to another

    Hello everyone,

    Earlier have asked questions about pulling data from yahoo. Have resolved that problem. Now trying to extract multiple data from one sheet to another there am not getting full success
    Let me try to explain you all
    I have a "Download" sheet Column "A" has Stocklist when am clicking the "Download All" button it extracts all stock data and the Last 5 days' data shows to K5:P10 and that data copy and paste to the "Calculation" sheet Column "A:F" which also getting paste.

    But here my problem start

    The "Download" Sheet doing its job perfectly but the "calculation" sheet does not paste correctly, only the last stock pastes fully 5 days of data and the rest only pastes 1 day of data. This is incorrect 5 days of data for each stock need to be pasted into the "calculation" sheet.

    The Excel file is uploaded for your reference, please help me to solve this problem.

    Thanks in advance

    VBA Code

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by vvishalb; 08-09-2022 at 03:55 AM.

  3. #3
    Registered User
    Join Date
    08-07-2012
    Location
    India
    MS-Off Ver
    Microsoft office home and student 2016 (Windows 10 Home 64bit)
    Posts
    98

    Re: Pull Yahoo Multiple stock data (Old version code not working)

    Hi,
    I did edit post add code but gave the same error while saving changes

    This page isn?t working www.excelforum.com is currently unable to handle this request.
    HTTP ERROR 500



    So please allow others to solve my query, please. And as it is have already uploaded my xlsm file for reference.

  4. #4
    Registered User
    Join Date
    08-07-2012
    Location
    India
    MS-Off Ver
    Microsoft office home and student 2016 (Windows 10 Home 64bit)
    Posts
    98

    Re: Extract multiple data from one sheet to another

    Hello everyone,

    Please any solution. Help me out, guys. please

  5. #5
    Registered User
    Join Date
    08-07-2012
    Location
    India
    MS-Off Ver
    Microsoft office home and student 2016 (Windows 10 Home 64bit)
    Posts
    98

    Re: Extract multiple data from one sheet to another

    Mr.bsalv, Mr. bebo021999, and other members,

    Please help me out, please am not getting how to solve this problem.

  6. #6
    Registered User
    Join Date
    08-07-2012
    Location
    India
    MS-Off Ver
    Microsoft office home and student 2016 (Windows 10 Home 64bit)
    Posts
    98

    Re: Pull Yahoo Multiple stock data (Old version code not working)

    No answer found yet so have posted the same query on another forum
    below is the link
    https://www.mrexcel.com/board/thread...other.1212987/


    please friends VBA experts provide me with a great solution.

    Thank you in advance

  7. #7
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: Pull Yahoo Multiple stock data (Old version code not working)

    you see this construction twice, once in the loop and once in the macro "init", and yes, it only pastes K3:P10
    Please Login or Register  to view this content.
    this is an alternative.
    Please Login or Register  to view this content.
    Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

  8. #8
    Registered User
    Join Date
    08-07-2012
    Location
    India
    MS-Off Ver
    Microsoft office home and student 2016 (Windows 10 Home 64bit)
    Posts
    98

    Re: Pull Yahoo Multiple stock data (Old version code not working)

    thank you brother, you replied

    I have just copied and pasted your code, but the calculation sheet is not giving me the expected results. Or maybe am doing wrong so attaching a file for your reference

    Every symbol should copy (K5:P10) and paste the calculation sheet but the last symbol only understand the command. others only understand single-day data i.e. (K5:P5)

    Thank you once again
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: Pull Yahoo Multiple stock data (Old version code not working)

    i can't test the loop, perhaps because i don't have the aPI-key
    What do you want ? only the 6 line K5:P10 for each Script or all the entries C8:Gx to "calculations.

    This macro now copies the actual content of C8:G255 to Calculations
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-07-2012
    Location
    India
    MS-Off Ver
    Microsoft office home and student 2016 (Windows 10 Home 64bit)
    Posts
    98

    Re: Pull Yahoo Multiple stock data (Old version code not working)

    Ohh.. but am not using any API key it's just free yahoo data.

    Only the 6-line K5:P10 for each Script paste in the "calculation" sheet
    sorry am troubling you a lot.

  11. #11
    Registered User
    Join Date
    08-07-2012
    Location
    India
    MS-Off Ver
    Microsoft office home and student 2016 (Windows 10 Home 64bit)
    Posts
    98

    Re: Pull Yahoo Multiple stock data (Old version code not working)

    Hey brother you tested mmm. any solution, please

  12. #12
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: Pull Yahoo Multiple stock data (Old version code not working)

    when you use the macros in the file, the result are errors and my skills aren't that good with internet-exchange, so, sorry.

  13. #13
    Registered User
    Join Date
    08-07-2012
    Location
    India
    MS-Off Ver
    Microsoft office home and student 2016 (Windows 10 Home 64bit)
    Posts
    98

    Re: Pull Yahoo Multiple stock data (Old version code not working)

    no problem. thank you very much

  14. #14
    Registered User
    Join Date
    08-07-2012
    Location
    India
    MS-Off Ver
    Microsoft office home and student 2016 (Windows 10 Home 64bit)
    Posts
    98

    Re: Pull Yahoo Multiple stock data (Old version code not working)

    Friends if anyone could help me out, please

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. YAHOO moved their Stock Data to a new folder
    By Mustang65 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-04-2017, 02:42 PM
  2. [SOLVED] Yahoo Stock History Data API not working?
    By Mustang65 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-19-2017, 11:24 AM
  3. Pull Share (Stock) Prices from Yahoo! Finance
    By DJB in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-11-2015, 09:13 AM
  4. VBA Code to pull Stock Balance Sheet Data from a Stock Symbol in a cell
    By akash1229 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 02-27-2015, 04:37 PM
  5. Yahoo Stock Price Data / Limited version
    By fred3 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-19-2014, 06:18 PM
  6. Gathering Stock Data from Yahoo MACRO help
    By denisl in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-11-2012, 04:39 PM
  7. Pull stock Data from Yahoo...Excel 2010
    By wcredle in forum Excel General
    Replies: 1
    Last Post: 12-22-2011, 03:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1