Solved

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

Posted on 2011-02-17
11
806 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

628 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