Pulling data from website based on cell value

H

hakeemshabazz

Ok, I'm not sure if my Subject is clear, because I am not entirely
sure what is involved in what I want to do. Bare with me, I'll try to
explain myself as clear as possible. :)

I downloaded a spreadsheet used for analyzing investments. It had a
cool feature that I want to duplicate for myself. In the spreadsheet,
you would input the company's stock symbol, hit a button, and then
certain worksheets would be updated with income statment, balance
sheet data from MSN's investment site.

I have tried duplicating this, only to realize I haven't a clue how it
is done. I can create a button, a macro, pull data off a website, but
I can't do any of these in connection with each other. Are there
steps to go through, or is this done with VBA coding?

The one part I have been able to accomplish is having a button trigger
a macro that pulls an income statement off the web...however, it's the
same company's income statment (haven't figured out how to have a cell
value point the macro's direction to the web data for any given
company).

I appreciate any replies to this post. I have done plenty of
searches, but given I'm not sure how this is done in the first place,
it's hard to do a search. :p If anyone can just clue me in on which
direction to focus on (vba coding, etc) that would be great...if
anyone can actually walk me through it, that would be awesome, but not
expected.

Thank you very much.
 
H

hakeemshabazz

Jeff, thanks for the link. I checked it out, and it's leading me in
the right direction. Using the info from the page, I was able to
setup a button that triggered the macro, asking for a stock symbol to
update. Awesome. Difference between what I have done & want to
accomplish is: right now, you click on a button to get a window asking
for the symbol...whereas the spreadsheet I'm trying to mimic has a
cell that you enter a value in, then click the button (like the
button/macro is pulling the cell value to use). Both ways serve my
purpose, I'm only curious how to do it like the spreadsheet...for my
own education.

Below is the code I pulled from the link. Below it, is the code that
I pulled from the spreadsheet I wish to mimic (which doesn't look
entirely the same). If you can (or anyone else) describe what the
spreadsheet author is doing compared to what I just learned from the
MSDN Library, I would appreciate it. ...just point me in the right
direction & I'll go from there. Hope I'm not asking for too much
help. ...or getting in way over my head :)

Thanks a bunch, again.

-MIKE
 
H

hakeemshabazz

Oh boy, just realized that I forgot to paste the code, haha. (just
like when you send an email talking about an attachment, then forget
to actually attach the file!)

Sub URL_Get_Query()
With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://quote.money.cnn.com/quote/quote?symbols=[""QUOTE"",""Enter
stock symbols separated by commas.""]", _
Destination:=Range("a1"))

.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub
================================================

Sheets("CurrentQuote").Select
Range("A1:D2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Income Statement(A-H)").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Income Statement(Q-H)").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Balance Sheet(A-H)").Select
ActiveWindow.ScrollRow = 1
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Cells.Replace What:="Mil", Replacement:="", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Cells.Replace What:="Bil", Replacement:="", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Sheets("Balance Sheet(Q-H)").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Cells.Replace What:="Mil", Replacement:="", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Cells.Replace What:="Bil", Replacement:="", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Sheets("Cash Flow Statement(A-H)").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Cash Flow Statement(Q-H)").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("10 Yr Statement Summary").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Key Ratios").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Statement and Quote Analysis").Select
Range("E1").Select
 
Top