To Whomever Knows the Answer:

I wrote a small program that goes out to the internet and looks up the
historical prices over a 10 year period from Yahoo! Finance
(www.finance.yahoo.com). The data can be found on the following URL:
http://finance.yahoo.com/q/hp?s=LLTC (you can input any ticker symbol
you want). From the URL you can change the date range, click "Get
Prices", and then scroll to the bottom of the page and click "Download
To Spreadsheet." A window then pops up asking if you want to open or
save the file. (The window's characteristics are Name: table.csv;
Type: Microsoft Excel Worksheet; From: ichart.finance.yahoo.com). If
you click open, the table will open inside the internet browser and the
aesthetics of the page look similar to Excel itself. At this point you
can copy the data set and paste it into Excel.

I'm wondering if there is a way to automate the above mentioned
process. Below you will find code that takes you as far as the pop-up
window. I'm not sure what to do after that. I'm making a wild guess
that there is a way to recognize the pop-up window, manipulate the
pop-up window, and then copy the output from the internet explorer to
Excel.

I wrote a function in order to grab two place holding values for date
information. The source code for the
http://finance.yahoo.com/q/hp?s=LLTC website reads January as a 00,
February as 01... and the function is the only way I could quickly
think of to have the program maintain 2 place holders rather than
interpreting a 00 as 0.

Any help is most appreciated.

Thank You,

Matt

Sub yahooPrices()

Dim ie As Object
Dim a 'start month
Dim b 'start day
Dim c 'start year
Dim d 'end month
Dim e 'end day
Dim f 'end year
Dim tic 'ticker
Dim mon 'month
Dim dy 'day
Dim yr 'year

Sheets("Yahoo Price Drop").Select

tic = UCase(InputBox("Enter the desire ticker for 10 years of
historical prices.", "Ticker", "APOL"))

mon = Format(DateSerial(Year(Date), Month(Date), day(Date)), "mmm")
dy = Format(DateSerial(Year(Date), Month(Date), day(Date)), "dd")
yr = Format(DateSerial(Year(Date), Month(Date), day(Date)), "yyyy")

'make sure single digit dates are 0x and not just x (e.g. 03 < 3)

a = monthToNumber(mon)
b = dy
c = yr - 10

d = a
e = b
f = yr

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.navigate "http://ichart.finance.yahoo.com/table.csv?s=" & tic &
"&a=" & a & "&b=" & b & "&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f &
"&g=d&ignore=.csv"

'Application.DisplayAlerts = False
'SendKeys "%o"

End Sub

Private Function monthToNumber(ByVal mon)

Select Case mon
Case "Jan"
monthToNumber = Right(100, 2)
Case "Feb"
monthToNumber = Right(101, 2)
Case "Mar"
monthToNumber = Right(102, 2)
Case "Apr"
monthToNumber = Right(103, 2)
Case "May"
monthToNumber = Right(104, 2)
Case "Jun"
monthToNumber = Right(105, 2)
Case "Jul"
monthToNumber = Right(106, 2)
Case "Aug"
monthToNumber = Right(107, 2)
Case "Sep"
monthToNumber = Right(108, 2)
Case "Oct"
monthToNumber = Right(109, 2)
Case "Nov"
monthToNumber = Right(110, 2)
Case "Dec"
monthToNumber = Right(111, 2)
End Select

End Function

What about using a web query ?

Looking at the URL that actual sent for a particular date range:
http://finance.yahoo.com/q/hp?s=LLTC...=24&f=2007&g=d

and allowing for the extra pages with the z and y parameters
http://finance.yahoo.com/q/hp?s=LLTC...d&z= 66&y=132

So you can:
- Amend the web query's Connection string
- Refresh
- Copy the data somewhere suitable
- Repeat adding the z & y parameter until no more data is returned

Adapt this:

Private Sub CommandButton1_Click()
Dim QT As QueryTable

Set QT = Worksheets(1).QueryTables(1)

With QT
Debug.Print .Connection
.Connection = Replace(.Connection, "c=1990", "c=" & Range("D1").Value)
.Refresh False
End With

End Sub

Talking to the server requesting a download of the required .csv file is
another, but not with this technique.

NickHK

"matt" wrote in message
oups.com...
To Whomever Knows the Answer:

I wrote a small program that goes out to the internet and looks up the
historical prices over a 10 year period from Yahoo! Finance
(www.finance.yahoo.com). The data can be found on the following URL:
http://finance.yahoo.com/q/hp?s=LLTC (you can input any ticker symbol
you want). From the URL you can change the date range, click "Get
Prices", and then scroll to the bottom of the page and click "Download
To Spreadsheet." A window then pops up asking if you want to open or
save the file. (The window's characteristics are Name: table.csv;
Type: Microsoft Excel Worksheet; From: ichart.finance.yahoo.com). If
you click open, the table will open inside the internet browser and the
aesthetics of the page look similar to Excel itself. At this point you
can copy the data set and paste it into Excel.

I'm wondering if there is a way to automate the above mentioned
process. Below you will find code that takes you as far as the pop-up
window. I'm not sure what to do after that. I'm making a wild guess
that there is a way to recognize the pop-up window, manipulate the
pop-up window, and then copy the output from the internet explorer to
Excel.

I wrote a function in order to grab two place holding values for date
information. The source code for the
http://finance.yahoo.com/q/hp?s=LLTC website reads January as a 00,
February as 01... and the function is the only way I could quickly
think of to have the program maintain 2 place holders rather than
interpreting a 00 as 0.

Any help is most appreciated.

Thank You,

Matt

Sub yahooPrices()

Dim ie As Object
Dim a 'start month
Dim b 'start day
Dim c 'start year
Dim d 'end month
Dim e 'end day
Dim f 'end year
Dim tic 'ticker
Dim mon 'month
Dim dy 'day
Dim yr 'year

Sheets("Yahoo Price Drop").Select

tic = UCase(InputBox("Enter the desire ticker for 10 years of
historical prices.", "Ticker", "APOL"))

mon = Format(DateSerial(Year(Date), Month(Date), day(Date)), "mmm")
dy = Format(DateSerial(Year(Date), Month(Date), day(Date)), "dd")
yr = Format(DateSerial(Year(Date), Month(Date), day(Date)), "yyyy")

'make sure single digit dates are 0x and not just x (e.g. 03 < 3)

a = monthToNumber(mon)
b = dy
c = yr - 10

d = a
e = b
f = yr

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.navigate "http://ichart.finance.yahoo.com/table.csv?s=" & tic &
"&a=" & a & "&b=" & b & "&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f &
"&g=d&ignore=.csv"

'Application.DisplayAlerts = False
'SendKeys "%o"

End Sub

Private Function monthToNumber(ByVal mon)

Select Case mon
Case "Jan"
monthToNumber = Right(100, 2)
Case "Feb"
monthToNumber = Right(101, 2)
Case "Mar"
monthToNumber = Right(102, 2)
Case "Apr"
monthToNumber = Right(103, 2)
Case "May"
monthToNumber = Right(104, 2)
Case "Jun"
monthToNumber = Right(105, 2)
Case "Jul"
monthToNumber = Right(106, 2)
Case "Aug"
monthToNumber = Right(107, 2)
Case "Sep"
monthToNumber = Right(108, 2)
Case "Oct"
monthToNumber = Right(109, 2)
Case "Nov"
monthToNumber = Right(110, 2)
Case "Dec"
monthToNumber = Right(111, 2)
End Select

End Function

How about when asked if you want to open or save the file,
you respond "save" and then give a location. You can then
open the save table.csv, copy the data, paste it to your
sheet "Yahoo Price Drop", and then delete table.csv.

Add code such as follows to yahooPrices():

Workbooks.Open Filename:="c:\temp\table.csv"
Workbooks("table.csv").Sheets("table").Range("A1:G 2516").Copy _
Destination:=Workbooks("Book1.xls").Sheets("Yahoo Price
Drop").Range("A1:G2516")
Workbooks("table.csv").Close
Kill "c:\temp\table.csv"

Hth,
Merjet

Nick,

Thanks for the help. I'm not an Excel VBA wiz by any means, and I'm
just now starting to get into IE automation and VBA/Internet
interactions. I played around with the y and z parameters in the URL
to try and understand how the website interprets the numbers.

I am interested though in learning more about web queries and as you
put it "talking to the server." If you know of any literature or can
point me in the right direction I'd greatly appreciate it.

The code that I wrote using your suggestions is below. I'm sure
there's a better way to do this, but it's working for now.

Tough the query gives me what I'm looking for, it takes longer than
navigating IE, telling the CSV file to Open, and then performing a
copy/paste. Additionally, the query brings the data in with items that
I'm not concerned about (i.e. deleting the - * Close price adjusted for
dividends and splits, Date...Open...High..., Splits, and Dividends
rows); however, this is an easy fix because I can write another small
macro to delete the unnecessary data.

In this scenario, the CSV file lumps all the data points together and
does not include splits or dividend lines. My main concern for my
analysis is the date and adjusted close columns.

Anyhow, I included the revised code below so that you can see what I
did. Again, I appreciate the help.

Thanks,

Matt

Sub Macro1()

Dim a 'start month
Dim b 'start day
Dim c 'start year
Dim d 'end month
Dim e 'end day
Dim f 'end year
Dim y
Dim z
Dim tic 'ticker
Dim mon 'month
Dim dy 'day
Dim yr 'year
Dim QT As QueryTable
Dim move
Dim outputCounter

Sheets(1).Select

tic = UCase(InputBox("Enter the desired ticker for 10 years of
historical prices.", "Ticker", "APOL"))

mon = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mmm")
dy = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd")
yr = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "yyyy")

'make sure single digit dates are 0x and not just x (e.g. 03 < 3)

a = monthToNumber(mon)
b = dy
c = yr - 10

d = a
e = b
f = yr

y = 0
z = 0

'Set ie = CreateObject("InternetExplorer.Application")
' ie.Visible = True
' ie.navigate "http://ichart.finance.yahoo.com/table.csv?s=" & tic &
"&a=" & a & "&b=" & b & "&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f &
"&g=d&ignore=.csv"
'End Sub

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/hp?s=" & tic & "&a=" & a &
"&b=" & b & "&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f & "&g=d&z=" &
z & "&y=" & y _
, Destination:=Range("A1"))
'.Name = "hp?s=LLTC&a=00&b=24&c=1997&d=00&e=24&f=2007&g =d"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xl
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Set QT = Worksheets(1).QueryTables(1)

'copy current data
Worksheets(1).Select
Range("a1").CurrentRegion.Copy
Worksheets(2).Select
outputCounter = Range("a1").CurrentRegion.Rows.Count
Range("a" & outputCounter + 1).PasteSpecial Paste:=xlPasteValues

'cycle back in time
For move = 66 To 3130 Step 66

Worksheets(1).Select

With QT
Debug.Print .Connection
.Connection = Replace(.Connection, "z=" & move - 66, "z=" & move)
.Connection = Replace(.Connection, "y=" & move - 66, "y=" & move)
'.BackgroundQuery = True
.Refresh False
End With

If Range("a2").Value = "" Then 'isempty(range("a2").value)
Exit For
Else
Range("a1").CurrentRegion.Copy
Worksheets(2).Select
outputCounter = Range("a1").CurrentRegion.Rows.Count
Range("a" & outputCounter + 1).PasteSpecial Paste:=xlPasteValues
End If
Next

Worksheets(2).Range("a1").Select

End Sub

Private Function monthToNumber(ByVal mon)

Select Case mon
Case "Jan"
monthToNumber = Right(100, 2)
Case "Feb"
monthToNumber = Right(101, 2)
Case "Mar"
monthToNumber = Right(102, 2)
Case "Apr"
monthToNumber = Right(103, 2)
Case "May"
monthToNumber = Right(104, 2)
Case "Jun"
monthToNumber = Right(105, 2)
Case "Jul"
monthToNumber = Right(106, 2)
Case "Aug"
monthToNumber = Right(107, 2)
Case "Sep"
monthToNumber = Right(108, 2)
Case "Oct"
monthToNumber = Right(109, 2)
Case "Nov"
monthToNumber = Right(110, 2)
Case "Dec"
monthToNumber = Right(111, 2)
End Select

End Function

On Jan 23, 7:11 pm, "NickHK" wrote:
What about using a web query ?

Looking at the URL that actual sent for a particular date range:http://finance.yahoo.com/q/hp?s=LLTC...=00&e=24&f=200...

and allowing for the extra pages with the z and y parametershttp://finance.yahoo.com/q/hp?s=LLTC&a=02&b=26&c=1990&d=00&e=24&f=200...

So you can:
- Amend the web query's Connection string
- Refresh
- Copy the data somewhere suitable
- Repeat adding the z & y parameter until no more data is returned

Adapt this:

Private Sub CommandButton1_Click()
Dim QT As QueryTable

Set QT = Worksheets(1).QueryTables(1)

With QT
Debug.Print .Connection
.Connection = Replace(.Connection, "c=1990", "c=" & Range("D1").Value)
.Refresh False
End With

End Sub

Talking to the server requesting a download of the required .csv file is
another, but not with this technique.

NickHK

"matt" wrote in ooglegroups.com...



To Whomever Knows the Answer:

I wrote a small program that goes out to the internet and looks up the
historical prices over a 10 year period from Yahoo! Finance
(www.finance.yahoo.com). The data can be found on the following URL:
http://finance.yahoo.com/q/hp?s=LLTC(you can input any ticker symbol
you want). From the URL you can change the date range, click "Get
Prices", and then scroll to the bottom of the page and click "Download
To Spreadsheet." A window then pops up asking if you want to open or
save the file. (The window's characteristics are Name: table.csv;
Type: Microsoft Excel Worksheet; From: ichart.finance.yahoo.com). If
you click open, the table will open inside the internet browser and the
aesthetics of the page look similar to Excel itself. At this point you
can copy the data set and paste it into Excel.

I'm wondering if there is a way to automate the above mentioned
process. Below you will find code that takes you as far as the pop-up
window. I'm not sure what to do after that. I'm making a wild guess
that there is a way to recognize the pop-up window, manipulate the
pop-up window, and then copy the output from the internet explorer to
Excel.

I wrote a function in order to grab two place holding values for date
information. The source code for the
http://finance.yahoo.com/q/hp?s=LLTCwebsite reads January as a 00,
February as 01... and the function is the only way I could quickly
think of to have the program maintain 2 place holders rather than
interpreting a 00 as 0.

Any help is most appreciated.

Thank You,

Matt

Sub yahooPrices()

Dim ie As Object
Dim a 'start month
Dim b 'start day
Dim c 'start year
Dim d 'end month
Dim e 'end day
Dim f 'end year
Dim tic 'ticker
Dim mon 'month
Dim dy 'day
Dim yr 'year

Sheets("Yahoo Price Drop").Select

tic = UCase(InputBox("Enter the desire ticker for 10 years of
historical prices.", "Ticker", "APOL"))

mon = Format(DateSerial(Year(Date), Month(Date), day(Date)), "mmm")
dy = Format(DateSerial(Year(Date), Month(Date), day(Date)), "dd")
yr = Format(DateSerial(Year(Date), Month(Date), day(Date)), "yyyy")

'make sure single digit dates are 0x and not just x (e.g. 03 < 3)

a = monthToNumber(mon)
b = dy
c = yr - 10

d = a
e = b
f = yr

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.navigate "http://ichart.finance.yahoo.com/table.csv?s=" & tic &
"&a=" & a & "&b=" & b & "&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f &
"&g=d&ignore=.csv"

'Application.DisplayAlerts = False
'SendKeys "%o"

End Sub

Private Function monthToNumber(ByVal mon)

Select Case mon
Case "Jan"
monthToNumber = Right(100, 2)
Case "Feb"
monthToNumber = Right(101, 2)
Case "Mar"
monthToNumber = Right(102, 2)
Case "Apr"
monthToNumber = Right(103, 2)
Case "May"
monthToNumber = Right(104, 2)
Case "Jun"
monthToNumber = Right(105, 2)
Case "Jul"
monthToNumber = Right(106, 2)
Case "Aug"
monthToNumber = Right(107, 2)
Case "Sep"
monthToNumber = Right(108, 2)
Case "Oct"
monthToNumber = Right(109, 2)
Case "Nov"
monthToNumber = Right(110, 2)
Case "Dec"
monthToNumber = Right(111, 2)
End Select

End Function- Hide quoted text -- Show quoted text -

Matt,
I'm no web expert, but may be check for using "HTTP request".

NickHK

"matt" wrote in message
oups.com...
Nick,

Thanks for the help. I'm not an Excel VBA wiz by any means, and I'm
just now starting to get into IE automation and VBA/Internet
interactions. I played around with the y and z parameters in the URL
to try and understand how the website interprets the numbers.

I am interested though in learning more about web queries and as you
put it "talking to the server." If you know of any literature or can
point me in the right direction I'd greatly appreciate it.

The code that I wrote using your suggestions is below. I'm sure
there's a better way to do this, but it's working for now.

Tough the query gives me what I'm looking for, it takes longer than
navigating IE, telling the CSV file to Open, and then performing a
copy/paste. Additionally, the query brings the data in with items that
I'm not concerned about (i.e. deleting the - * Close price adjusted for
dividends and splits, Date...Open...High..., Splits, and Dividends
rows); however, this is an easy fix because I can write another small
macro to delete the unnecessary data.

In this scenario, the CSV file lumps all the data points together and
does not include splits or dividend lines. My main concern for my
analysis is the date and adjusted close columns.

Anyhow, I included the revised code below so that you can see what I
did. Again, I appreciate the help.

Thanks,

Matt

Sub Macro1()

Dim a 'start month
Dim b 'start day
Dim c 'start year
Dim d 'end month
Dim e 'end day
Dim f 'end year
Dim y
Dim z
Dim tic 'ticker
Dim mon 'month
Dim dy 'day
Dim yr 'year
Dim QT As QueryTable
Dim move
Dim outputCounter

Sheets(1).Select

tic = UCase(InputBox("Enter the desired ticker for 10 years of
historical prices.", "Ticker", "APOL"))

mon = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mmm")
dy = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd")
yr = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "yyyy")

'make sure single digit dates are 0x and not just x (e.g. 03 < 3)

a = monthToNumber(mon)
b = dy
c = yr - 10

d = a
e = b
f = yr

y = 0
z = 0

'Set ie = CreateObject("InternetExplorer.Application")
' ie.Visible = True
' ie.navigate "http://ichart.finance.yahoo.com/table.csv?s=" & tic &
"&a=" & a & "&b=" & b & "&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f &
"&g=d&ignore=.csv"
'End Sub

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/hp?s=" & tic & "&a=" & a &
"&b=" & b & "&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f & "&g=d&z=" &
z & "&y=" & y _
, Destination:=Range("A1"))
'.Name = "hp?s=LLTC&a=00&b=24&c=1997&d=00&e=24&f=2007&g =d"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xl
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Set QT = Worksheets(1).QueryTables(1)

'copy current data
Worksheets(1).Select
Range("a1").CurrentRegion.Copy
Worksheets(2).Select
outputCounter = Range("a1").CurrentRegion.Rows.Count
Range("a" & outputCounter + 1).PasteSpecial Paste:=xlPasteValues

'cycle back in time
For move = 66 To 3130 Step 66

Worksheets(1).Select

With QT
Debug.Print .Connection
.Connection = Replace(.Connection, "z=" & move - 66, "z=" & move)
.Connection = Replace(.Connection, "y=" & move - 66, "y=" & move)
'.BackgroundQuery = True
.Refresh False
End With

If Range("a2").Value = "" Then 'isempty(range("a2").value)
Exit For
Else
Range("a1").CurrentRegion.Copy
Worksheets(2).Select
outputCounter = Range("a1").CurrentRegion.Rows.Count
Range("a" & outputCounter + 1).PasteSpecial Paste:=xlPasteValues
End If
Next

Worksheets(2).Range("a1").Select

End Sub

Private Function monthToNumber(ByVal mon)

Select Case mon
Case "Jan"
monthToNumber = Right(100, 2)
Case "Feb"
monthToNumber = Right(101, 2)
Case "Mar"
monthToNumber = Right(102, 2)
Case "Apr"
monthToNumber = Right(103, 2)
Case "May"
monthToNumber = Right(104, 2)
Case "Jun"
monthToNumber = Right(105, 2)
Case "Jul"
monthToNumber = Right(106, 2)
Case "Aug"
monthToNumber = Right(107, 2)
Case "Sep"
monthToNumber = Right(108, 2)
Case "Oct"
monthToNumber = Right(109, 2)
Case "Nov"
monthToNumber = Right(110, 2)
Case "Dec"
monthToNumber = Right(111, 2)
End Select

End Function

On Jan 23, 7:11 pm, "NickHK" wrote:
What about using a web query ?

Looking at the URL that actual sent for a particular date
range:http://finance.yahoo.com/q/hp?s=LLTC...=00&e=24&f=200.
...

and allowing for the extra pages with the z and y
parametershttp://finance.yahoo.com/q/hp?s=LLTC&a=02&b=26&c=1990&d=00&e=24&f=
200...

So you can:
- Amend the web query's Connection string
- Refresh
- Copy the data somewhere suitable
- Repeat adding the z & y parameter until no more data is returned

Adapt this:

Private Sub CommandButton1_Click()
Dim QT As QueryTable

Set QT = Worksheets(1).QueryTables(1)

With QT
Debug.Print .Connection
.Connection = Replace(.Connection, "c=1990", "c=" &
Range("D1").Value)
.Refresh False
End With

End Sub

Talking to the server requesting a download of the required .csv file is
another, but not with this technique.

NickHK

"matt" wrote in
ooglegroups.com...



To Whomever Knows the Answer:

I wrote a small program that goes out to the internet and looks up the
historical prices over a 10 year period from Yahoo! Finance
(www.finance.yahoo.com). The data can be found on the following URL:
http://finance.yahoo.com/q/hp?s=LLTC(you can input any ticker symbol
you want). From the URL you can change the date range, click "Get
Prices", and then scroll to the bottom of the page and click "Download
To Spreadsheet." A window then pops up asking if you want to open or
save the file. (The window's characteristics are Name: table.csv;
Type: Microsoft Excel Worksheet; From: ichart.finance.yahoo.com). If
you click open, the table will open inside the internet browser and
the
aesthetics of the page look similar to Excel itself. At this point
you
can copy the data set and paste it into Excel.

I'm wondering if there is a way to automate the above mentioned
process. Below you will find code that takes you as far as the pop-up
window. I'm not sure what to do after that. I'm making a wild guess
that there is a way to recognize the pop-up window, manipulate the
pop-up window, and then copy the output from the internet explorer to
Excel.

I wrote a function in order to grab two place holding values for date
information. The source code for the
http://finance.yahoo.com/q/hp?s=LLTCwebsite reads January as a 00,
February as 01... and the function is the only way I could quickly
think of to have the program maintain 2 place holders rather than
interpreting a 00 as 0.

Any help is most appreciated.

Thank You,

Matt

Sub yahooPrices()

Dim ie As Object
Dim a 'start month
Dim b 'start day
Dim c 'start year
Dim d 'end month
Dim e 'end day
Dim f 'end year
Dim tic 'ticker
Dim mon 'month
Dim dy 'day
Dim yr 'year

Sheets("Yahoo Price Drop").Select

tic = UCase(InputBox("Enter the desire ticker for 10 years of
historical prices.", "Ticker", "APOL"))

mon = Format(DateSerial(Year(Date), Month(Date), day(Date)), "mmm")
dy = Format(DateSerial(Year(Date), Month(Date), day(Date)), "dd")
yr = Format(DateSerial(Year(Date), Month(Date), day(Date)), "yyyy")

'make sure single digit dates are 0x and not just x (e.g. 03 < 3)

a = monthToNumber(mon)
b = dy
c = yr - 10

d = a
e = b
f = yr

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.navigate "http://ichart.finance.yahoo.com/table.csv?s=" & tic &
"&a=" & a & "&b=" & b & "&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f
&
"&g=d&ignore=.csv"

'Application.DisplayAlerts = False
'SendKeys "%o"

End Sub

Private Function monthToNumber(ByVal mon)

Select Case mon
Case "Jan"
monthToNumber = Right(100, 2)
Case "Feb"
monthToNumber = Right(101, 2)
Case "Mar"
monthToNumber = Right(102, 2)
Case "Apr"
monthToNumber = Right(103, 2)
Case "May"
monthToNumber = Right(104, 2)
Case "Jun"
monthToNumber = Right(105, 2)
Case "Jul"
monthToNumber = Right(106, 2)
Case "Aug"
monthToNumber = Right(107, 2)
Case "Sep"
monthToNumber = Right(108, 2)
Case "Oct"
monthToNumber = Right(109, 2)
Case "Nov"
monthToNumber = Right(110, 2)
Case "Dec"
monthToNumber = Right(111, 2)
End Select

End Function- Hide quoted text -- Show quoted text -

Matt,
Actually there is simple way get the WB downloaded:

Dim URL As String
Dim WB As Workbook

'Build your URL as required from symbol, start/end date etc from cell value
or where ever to give something like:
URL =
"http://ichart.finance.yahoo.com/table.csv?s=LLTC&d=0&e=26&f=2007&g=d&a=2&b=
26&c=1990&ignore=.csv"

Set WB = Workbooks.Open(URL)
WB.SaveAs "C:\Some name.xls"

NickHK

"matt" wrote in message
oups.com...
Nick,

Thanks for the help. I'm not an Excel VBA wiz by any means, and I'm
just now starting to get into IE automation and VBA/Internet
interactions. I played around with the y and z parameters in the URL
to try and understand how the website interprets the numbers.

I am interested though in learning more about web queries and as you
put it "talking to the server." If you know of any literature or can
point me in the right direction I'd greatly appreciate it.

The code that I wrote using your suggestions is below. I'm sure
there's a better way to do this, but it's working for now.

Tough the query gives me what I'm looking for, it takes longer than
navigating IE, telling the CSV file to Open, and then performing a
copy/paste. Additionally, the query brings the data in with items that
I'm not concerned about (i.e. deleting the - * Close price adjusted for
dividends and splits, Date...Open...High..., Splits, and Dividends
rows); however, this is an easy fix because I can write another small
macro to delete the unnecessary data.

In this scenario, the CSV file lumps all the data points together and
does not include splits or dividend lines. My main concern for my
analysis is the date and adjusted close columns.

Anyhow, I included the revised code below so that you can see what I
did. Again, I appreciate the help.

Thanks,

Matt

Sub Macro1()

Dim a 'start month
Dim b 'start day
Dim c 'start year
Dim d 'end month
Dim e 'end day
Dim f 'end year
Dim y
Dim z
Dim tic 'ticker
Dim mon 'month
Dim dy 'day
Dim yr 'year
Dim QT As QueryTable
Dim move
Dim outputCounter

Sheets(1).Select

tic = UCase(InputBox("Enter the desired ticker for 10 years of
historical prices.", "Ticker", "APOL"))

mon = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mmm")
dy = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd")
yr = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "yyyy")

'make sure single digit dates are 0x and not just x (e.g. 03 < 3)

a = monthToNumber(mon)
b = dy
c = yr - 10

d = a
e = b
f = yr

y = 0
z = 0

'Set ie = CreateObject("InternetExplorer.Application")
' ie.Visible = True
' ie.navigate "http://ichart.finance.yahoo.com/table.csv?s=" & tic &
"&a=" & a & "&b=" & b & "&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f &
"&g=d&ignore=.csv"
'End Sub

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/hp?s=" & tic & "&a=" & a &
"&b=" & b & "&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f & "&g=d&z=" &
z & "&y=" & y _
, Destination:=Range("A1"))
'.Name = "hp?s=LLTC&a=00&b=24&c=1997&d=00&e=24&f=2007&g =d"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xl
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Set QT = Worksheets(1).QueryTables(1)

'copy current data
Worksheets(1).Select
Range("a1").CurrentRegion.Copy
Worksheets(2).Select
outputCounter = Range("a1").CurrentRegion.Rows.Count
Range("a" & outputCounter + 1).PasteSpecial Paste:=xlPasteValues

'cycle back in time
For move = 66 To 3130 Step 66

Worksheets(1).Select

With QT
Debug.Print .Connection
.Connection = Replace(.Connection, "z=" & move - 66, "z=" & move)
.Connection = Replace(.Connection, "y=" & move - 66, "y=" & move)
'.BackgroundQuery = True
.Refresh False
End With

If Range("a2").Value = "" Then 'isempty(range("a2").value)
Exit For
Else
Range("a1").CurrentRegion.Copy
Worksheets(2).Select
outputCounter = Range("a1").CurrentRegion.Rows.Count
Range("a" & outputCounter + 1).PasteSpecial Paste:=xlPasteValues
End If
Next

Worksheets(2).Range("a1").Select

End Sub

Private Function monthToNumber(ByVal mon)

Select Case mon
Case "Jan"
monthToNumber = Right(100, 2)
Case "Feb"
monthToNumber = Right(101, 2)
Case "Mar"
monthToNumber = Right(102, 2)
Case "Apr"
monthToNumber = Right(103, 2)
Case "May"
monthToNumber = Right(104, 2)
Case "Jun"
monthToNumber = Right(105, 2)
Case "Jul"
monthToNumber = Right(106, 2)
Case "Aug"
monthToNumber = Right(107, 2)
Case "Sep"
monthToNumber = Right(108, 2)
Case "Oct"
monthToNumber = Right(109, 2)
Case "Nov"
monthToNumber = Right(110, 2)
Case "Dec"
monthToNumber = Right(111, 2)
End Select

End Function

On Jan 23, 7:11 pm, "NickHK" wrote:
What about using a web query ?

Looking at the URL that actual sent for a particular date
range:http://finance.yahoo.com/q/hp?s=LLTC...=00&e=24&f=200.
...

and allowing for the extra pages with the z and y
parametershttp://finance.yahoo.com/q/hp?s=LLTC&a=02&b=26&c=1990&d=00&e=24&f=
200...

So you can:
- Amend the web query's Connection string
- Refresh
- Copy the data somewhere suitable
- Repeat adding the z & y parameter until no more data is returned

Adapt this:

Private Sub CommandButton1_Click()
Dim QT As QueryTable

Set QT = Worksheets(1).QueryTables(1)

With QT
Debug.Print .Connection
.Connection = Replace(.Connection, "c=1990", "c=" &
Range("D1").Value)
.Refresh False
End With

End Sub

Talking to the server requesting a download of the required .csv file is
another, but not with this technique.

NickHK

"matt" wrote in
ooglegroups.com...



To Whomever Knows the Answer:

I wrote a small program that goes out to the internet and looks up the
historical prices over a 10 year period from Yahoo! Finance
(www.finance.yahoo.com). The data can be found on the following URL:
http://finance.yahoo.com/q/hp?s=LLTC(you can input any ticker symbol
you want). From the URL you can change the date range, click "Get
Prices", and then scroll to the bottom of the page and click "Download
To Spreadsheet." A window then pops up asking if you want to open or
save the file. (The window's characteristics are Name: table.csv;
Type: Microsoft Excel Worksheet; From: ichart.finance.yahoo.com). If
you click open, the table will open inside the internet browser and
the
aesthetics of the page look similar to Excel itself. At this point
you
can copy the data set and paste it into Excel.

I'm wondering if there is a way to automate the above mentioned
process. Below you will find code that takes you as far as the pop-up
window. I'm not sure what to do after that. I'm making a wild guess
that there is a way to recognize the pop-up window, manipulate the
pop-up window, and then copy the output from the internet explorer to
Excel.

I wrote a function in order to grab two place holding values for date
information. The source code for the
http://finance.yahoo.com/q/hp?s=LLTCwebsite reads January as a 00,
February as 01... and the function is the only way I could quickly
think of to have the program maintain 2 place holders rather than
interpreting a 00 as 0.

Any help is most appreciated.

Thank You,

Matt

Sub yahooPrices()

Dim ie As Object
Dim a 'start month
Dim b 'start day
Dim c 'start year
Dim d 'end month
Dim e 'end day
Dim f 'end year
Dim tic 'ticker
Dim mon 'month
Dim dy 'day
Dim yr 'year

Sheets("Yahoo Price Drop").Select

tic = UCase(InputBox("Enter the desire ticker for 10 years of
historical prices.", "Ticker", "APOL"))

mon = Format(DateSerial(Year(Date), Month(Date), day(Date)), "mmm")
dy = Format(DateSerial(Year(Date), Month(Date), day(Date)), "dd")
yr = Format(DateSerial(Year(Date), Month(Date), day(Date)), "yyyy")

'make sure single digit dates are 0x and not just x (e.g. 03 < 3)

a = monthToNumber(mon)
b = dy
c = yr - 10

d = a
e = b
f = yr

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.navigate "http://ichart.finance.yahoo.com/table.csv?s=" & tic &
"&a=" & a & "&b=" & b & "&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f
&
"&g=d&ignore=.csv"

'Application.DisplayAlerts = False
'SendKeys "%o"

End Sub

Private Function monthToNumber(ByVal mon)

Select Case mon
Case "Jan"
monthToNumber = Right(100, 2)
Case "Feb"
monthToNumber = Right(101, 2)
Case "Mar"
monthToNumber = Right(102, 2)
Case "Apr"
monthToNumber = Right(103, 2)
Case "May"
monthToNumber = Right(104, 2)
Case "Jun"
monthToNumber = Right(105, 2)
Case "Jul"
monthToNumber = Right(106, 2)
Case "Aug"
monthToNumber = Right(107, 2)
Case "Sep"
monthToNumber = Right(108, 2)
Case "Oct"
monthToNumber = Right(109, 2)
Case "Nov"
monthToNumber = Right(110, 2)
Case "Dec"
monthToNumber = Right(111, 2)
End Select

End Function- Hide quoted text -- Show quoted text -