Solved

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

Posted on 2011-02-17
11
803 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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

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!
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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…

679 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