How do I use Data Query from Web to bring in more data?

I have a list of 5 stocks whose stock price I want to put into cells B1 through B5.  I know how to import data "From Web" using a web query with this address (I made one for each of the 5 stocks):

How do I make it so that if I add more symbols to my excel worksheet I can use autofill on the webqueries and have it automatically bring in the quotes?  I want to avoid having to create each and every separate web query for every additional stock I want to add to my worksheet.
Who is Participating?
Eric ZwiekhorstConnect With a Mentor SAP Business ConsultantCommented:
Dear shaolinfunk

This version works correct, tryed it with some quotes of my one
just type your yahoo shortcut in column B and the quotes will appear..

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Target.Column = 2 Then 'only look at column B for changes of stock shortcuts
    Cells(1, 1).EntireColumn.Delete
    Cells(1, 1).EntireColumn.Insert
    i = 1 'begin row
    Cells(i, 1).Select 'first cell to look at
    While Cells(i, 2) > "a"
        rangestock = "B" & i

        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;" & Range(rangestock).Text & "&f=p", Destination _
            :=Range("A" & i))
            .Name = "quotes.csv?s=" & Range(rangestock).Text & "&f=p_1"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = True
            .BackgroundQuery = True
            .RefreshStyle = xlOverwriteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = False
            .RefreshPeriod = 0
            .WebSelectionType = xlAllTables
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=True
        End With

        i = i + 1
End If
Application.EnableEvents = True
End Sub
Eric ZwiekhorstSAP Business ConsultantCommented:
dear shoalinfunk,

Please  upload the csv to this site using this sites functionality.
I can not open your link..

Kind regards

shaolinfunkAuthor Commented:
Uh hi...that's the link you're supposed to put in the address field when using Excel 2007's Data "From Web" feature....
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

shaolinfunkAuthor Commented:
I "import" the data at that URL into my spreadsheet...I don't actually download any files....
Eric ZwiekhorstSAP Business ConsultantCommented:
Please have a look at this file.
In the column B you can place the tag of the stock (LIkE msft)
If there is the CSV in yahoo it will be in the column A
for to change a querry I have to delete the column A and insert a column.
Than all the web querry a delete.
Than the macro will see in column B the TAG and he makes a new web querry for that.

kind regard

Eric ZwiekhorstSAP Business ConsultantCommented:
if you change the cell in column B the macro will run automatic.

Kind regard

shaolinfunkAuthor Commented:
Hi Eric,

I typed "" into colum B1...and in A1 i get the error "N/A,"HTTP:","HTTP:",N/A"

Did you get something different?
shaolinfunkAuthor Commented:
my apologies! i see what you mean now...i should type in symbol in column B..give me a few more minutes to make sure i am doing this correctly!
shaolinfunkAuthor Commented:
Yes this is EXACTLY what I need.  THANK YOU VERY MUCH!
shaolinfunkAuthor Commented:

Instead of using Autofill and Worksheet_Change I would like to push a button that will run your macro.  I know how to create a button and assign a macro...but i do not know how to alter your code to make it work with the button.

Let's say I have all my stock symbols in column X and I want prices to appear in column Y.  Also, since I have headers in X1 ("stocks") and Y1 ("prices") how do I get your macro to NOT delete the entirecolumn?
shaolinfunkAuthor Commented:
should i post this as a new questions so you'll get more point?
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.

All Courses

From novice to tech pro — start learning today.