• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1258
  • Last Modified:

VBA Code to download historical stock quotes

I am using the following VBA code to download yahoo historical stock quotes.  The result puts the stock quote csv into a single cell (destination C6).  How would I modify the parameters so that each value in the table is put into a seperate cell?  Thanks in advance to anyone that can help.

qurl = "http://ichart.yahoo.com/table.csv?s=" & Symb
        qurl = qurl & "&a=9&b=6&c=2007&d=" & Month & "&e=" & _
            Day & "&f=" & Year & "&g=d&ignore=.csv"
       
        With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=Range("C6"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
             End With
0
DLW2
Asked:
DLW2
  • 2
  • 2
1 Solution
 
harr22Commented:
I do something very similar and just parse it out with "Text To Columns"  Here is my code that does the parsing, hard to tell from what you posted if this will work for you as is or if you have to tweak it.  You could just record a macro performing the text to columns function.

 
Columns("A:A").Select
                Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
                :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
                Array(7, 1)), TrailingMinusNumbers:=True
                Range("A:A").NumberFormat = "mm/dd/yyyy;@"
                Columns.AutoFit

Open in new window

0
 
DLW2Author Commented:
Harr22:

OK thanks.  I thought it was as simple as a missing parameter in the query tables statement but maybe not.  I will give your suggestion a try.  Dave
0
 
DLW2Author Commented:
Harr22:

Thank you - this worked.  I was not aware of the text to column feature.
0
 
harr22Commented:
OK thanks.  I thought it was as simple as a missing parameter in the query tables statement but maybe not.  I will give your suggestion a try.  Dave

I don't think you can make the query return it in multiple columns.  I fought with it for a while a couple years ago.  Glad you got the text to columns method to work out for you, its a pretty cool feature.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now