Need Help! web query macro.

[gger]

Hi Guys,

I am new to this so be patient. I am looking to create a button that pulls data from a website based on the content of a cell. I have attempted to use the macro recorder to copy the content of a cell, run a web query (to: http://www.financialpost.com/) and paste the copied cell value into the stock quote search box on the right hand side. I want it to select the ticker, go to financials and import the balance sheet to cell B15. Ideally, I would want it to import all the financials for that ticker (balance sheet, cash flow, income statement) and import it on the sheet with the data side by side.

I have run in to two problems:
1. I can get it to run the macro, but regardless of the content of the copied cell (the ticker), it will only return data from the ticker i used to create the macro.

2. If i click the button more than once, instead of clearing data from the earlier search it will return data next to the earlier data. I would prefer if it wiped the previous search and returned the data in B15.

Ultimately, my goal is to be able to input a ticker in a specific cell, press the "run" button, and have the income statement, cash flow statement, and balance sheet statement data pop up on my "Data" sheet.

This is my code at the moment:

Private Sub CommandButton1_Click()
'
' Macro5 Macro
' Macro recorded 05/01/2011 by gger
'

'
Range("B7").Select
ActiveCell.FormulaR1C1 = "AAPL"
Range("D14").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://idms.financialpost.com/stocks/balance_sheet.idms?SYMBOL=AAPL&ID_NOTATION=253929" _
, Destination:=Range("D14"))
.Name = "balance_sheet.idms?SYMBOL=AAPL&ID_NOTATION=253929"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """IDMS_BalanceSheet"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Any thoughts?
Thanks in advance,

gger.

 

[gger]

can anyone help me on this?

 

[John_w]

If the ticker symbol is in A1, try:

"URL;http://idms.financialpost.com/stocks/balance_sheet.idms?SYMBOL=" & Range("A1").Value & "&ID_NOTATION=253929"

To import all the data for the symbol, create 2 other web queries with the Macro Recorder, one for the cash flow data and one for the income statement, with different destination cells, and modify them as shown above.

 

[gger]

awesome! That was a ton of help.

I am still running in to problem #2 though... every time I press the button it drops the new data at the destination cell but pushes the old data over 6 cells (including the button)... is there any way I could have it replace the old data instead?

 

[John_w]

Just clear the rows with data. For example to clear all rows from row 3 downwards:

With ActiveSheet
.Rows("3:" & .Rows.Count).ClearContents
End With

 

[gger]

so at the beginning of my code i put this in

Range("A1:AD299").Select
Selection.ClearContents

and when i run the macro it returns a box saying "Run time error '1004': Select Method of Range Class failed.


...?

Just to recap, this is the code in its full form.

'
' Data Macro
' Macro recorded 06/01/2011 by gger
'

'

Sheets("Data").Select
Range("A1:AD299").Select
Selection.ClearContents
Sheets("NetNet").Select
Range("G2").Select
ActiveCell.FormulaR1C1 = "RIMM"
Sheets("Data").Select
Range("C11").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://idms.financialpost.com/stocks/balance_sheet.idms?SYMBOL=RIMM&ID_NOTATION=98948" _
, Destination:=Range("B9"))
.Name = "balance_sheet.idms?SYMBOL=RIMM&ID_NOTATION=98948"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """IDMS_BalanceSheet"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Range("H9").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://idms.financialpost.com/stocks/income_statement.idms?SYMBOL=RIMM&ID_NOTATION=98948" _
, Destination:=Range("H9"))
.Name = "income_statement.idms?SYMBOL=RIMM&ID_NOTATION=98948"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Range("N9").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://idms.financialpost.com/stocks/cash_flow.idms?SYMBOL=RIMM&ID_NOTATION=98948" _
, Destination:=Range("N9"))
.Name = "cash_flow.idms?SYMBOL=RIMM&ID_NOTATION=98948"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=-6
End Sub

 

[John_w]

Try:

Range("A1:AD299").ClearContents

 

[gger]

back to question #1. Your code works perfectly if my button is on the same sheet, if i move the button to a different one i need the code in the url to reference the other sheet and then the range ("B7")... how would i add that?

 

[gger]

nevermind ... fumbled my way through it.

Now that i have my numbers coming in properly, is there a way (through formulas, vba, etc) of getting rid of a part of each cell? my numbers are currently coming in as, for example, 15.4M.


I guess i am asking, is there a way i can make that 15.4M look like 15.4(without the 'M')?

 

[mariog]

nevermind ... fumbled my way through it.

Now that i have my numbers coming in properly, is there a way (through formulas, vba, etc) of getting rid of a part of each cell? my numbers are currently coming in as, for example, 15.4M.


I guess i am asking, is there a way i can make that 15.4M look like 15.4(without the 'M')?

With a formula:

=LEFT("CELL", LEN("CELL")-1)

Cell being well, the cell you'd like to change.