Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-02-17
11
Medium Priority
?
810 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 2000 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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

772 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