Solved

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

Posted on 2011-02-17
11
804 Views
Last Modified: 2012-05-11
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):

http://download.finance.yahoo.com/d/quotes.csv?s=msft&f=p

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.
0
Comment
Question by:shaolinfunk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34915813
dear shoalinfunk,

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

Kind regards

Eric
0
 
LVL 1

Author Comment

by:shaolinfunk
ID: 34915822
Uh hi...that's the link you're supposed to put in the address field when using Excel 2007's Data "From Web" feature....
0
 
LVL 1

Author Comment

by:shaolinfunk
ID: 34915825
I "import" the data at that URL into my spreadsheet...I don't actually download any files....
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34916134
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
quotes.xls
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34916142
if you change the cell in column B the macro will run automatic.

Kind regard

eric
0
 
LVL 6

Accepted Solution

by:
Eric Zwiekhorst earned 500 total points
ID: 34916552
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;http://download.finance.yahoo.com/d/quotes.csv?s=" & 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
    Wend
End If
Application.EnableEvents = True
End Sub
0
 
LVL 1

Author Comment

by:shaolinfunk
ID: 34917262
Hi Eric,

I typed "http://download.finance.yahoo.com/d/quotes.csv?s=msft&f=p" into colum B1...and in A1 i get the error "N/A,"HTTP:","HTTP:",N/A"

Did you get something different?
0
 
LVL 1

Author Comment

by:shaolinfunk
ID: 34917299
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!
0
 
LVL 1

Author Closing Comment

by:shaolinfunk
ID: 34917357
Yes this is EXACTLY what I need.  THANK YOU VERY MUCH!
0
 
LVL 1

Author Comment

by:shaolinfunk
ID: 34917505
Eric,

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?
0
 
LVL 1

Author Comment

by:shaolinfunk
ID: 34918247
should i post this as a new questions so you'll get more point?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question