Solved

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

Posted on 2011-02-17
11
798 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
Comment Utility
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
Comment Utility
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
Comment Utility
I "import" the data at that URL into my spreadsheet...I don't actually download any files....
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
Comment Utility
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
Comment Utility
if you change the cell in column B the macro will run automatic.

Kind regard

eric
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 6

Accepted Solution

by:
Eric Zwiekhorst earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Yes this is EXACTLY what I need.  THANK YOU VERY MUCH!
0
 
LVL 1

Author Comment

by:shaolinfunk
Comment Utility
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
Comment Utility
should i post this as a new questions so you'll get more point?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now