web query and changing URL's

[Guest]

I am writing macros that perform web queries to download stock data from
Anumati.com

the anumati.com format is like this:
http://www.anumati.com/DynamicReport.aspx?id=TR&report=IS-Q& dates _
=03/07/2004;03/04/2004;31/12/2003;27/09/2003;28/06/2003; _
29/03/2003;31/12/2002;28/09/2002

The dates change depending on the stock symbol you input (TR) in this case.
The way I have written the macro currently is to determine the year end that
the company is using, then assume quarterly ending dates based on the year.
ie. if the year end is Dec. 31, 2004, quarterly ending date would be March
31, June 30, Sept. 30, Dec. 31. I have accomodated year ends for each month
and the corresponding quarters (3 month periods - last day of month) based on
the year.

I am using string formulas to create a URL as follows:

A2 = http://www.anumati.com/DynamicReport.aspx?id=
A3 = Stock Symbol you entered (TR in the example above)
A4 = &report=
A5 = the report you requested (IS-Q = Income Stmt - Quarterly)
A6 = & dates=
A7 = The dates I have calculated using the determined year end

A1 = A2&A3&A4&A5&A6&A7
A1 is the string I use in the web query macro as the URL to download

As you can see from the URL above, the quarter end dates are not necessarily
the end of the month, and it is not necessarily 3 month periods. That is
where my logic breaks down.

Is there an easier to develop dynamic web queries (by that I mean tell
Anumati to show me the quarter data, regardless of what those periods
actually are). The string method I am using works great for most of the
companies, but there are always those that don't fit the mold.

Thanks. any help would be appreciated!

Joe V.

 

[Don Guillett]

You may prefer using http://finance.yahoo.com/q/is?s=tr

or,
As I see it you must be able to do one macro to get the dates on one sheet
and the second on another sheet to be modified from what you get on the 1st
sheet. Although I have not tried this specifically, this is the sort of
thing I sometimes do for clients. You would need to modify both of these to
set the sheet, etc. Somewhere, I may actually have one with an income
statement.


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/15/2004 by Don Guillett
'

'
With Selection.QueryTable
.Connection = "URL;http://www.anumati.com/StockHome.aspx?id=TR"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
End Sub

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/15/2004 by Don Guillett
'

'
With Selection.QueryTable
.Connection = _

"URL;http://www.anumati.com/DynamicReport.aspx?id=TR&report=IS-Q&dates=03/07
/2004;03/04/2004;31/12/2003;27/09/2003;28/06/2003;29/03/2003;31/12/2002;28/0
9/2002"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "5"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Don Guillett]

BTW. I discovered your earlier posts. This is NOT the way to win friends and
influence people around here. Post and wait.....

 

[Guest]

Sorry Don,

With each of my earlier posts, I received a netscape error that said there
was a problem with the data I sent, so I tried again, and again, and ....