PDA

View Full Version : VBA: how to interpret this code?



choubix
02-11-2008, 04:33 AM
hello,

I am really rusty in vba and I'd like to understand the following portion of code.
what it does: retrieve historical data from yahoo finance for a series of tickers.


Sub PHExample2()

Dim sTicker As String

[PHResults2].ClearContents
Application.Calculation = xlCalculationManual
Set objPH = PriceHistory

For i = 1 To [PHTickerGroup].Columns.Count

sTicker = [PHTickerGroup].Cells(1, i)
nNumItems = objPH.GetPriceHistory("yahoo", sTicker, [PHStartDate].Value, _
[PHEndDate].Value, [PHFrequency].Value, [PHAllDates].Value)

[PHError].Value = objPH.ErrorDescription
If nNumItems = 0 Then GoTo fin

For j = 1 To nNumItems
objPH.Item = j
If i = 1 Then [PHResults2].Cells(j, 1) = objPH.PriceDate
If objPH.priceclose <> 0 Then 'omit any non-trading days
[PHResults2].Cells(j, i + 1) = objPH.PriceAdjClose
End If
Next j

Next i

fin:
Application.Calculation = xlCalculationAutomatic
End Sub

What I don't understand:

- what are xlcalculationmanual and xlcalcualtionautomatic are for in this code?
- I don't fully understand how the 2nd loop works. it is supposed to remove non trading days but I don't understnad how this works.

hope someone can enlight me :)

thanks!
Alex

Bob Phillips
02-11-2008, 04:46 AM
It turns off automatic calculation as that will slow the process down, then turns it back on again at the end.

choubix
02-11-2008, 04:59 AM
hi xld

thanks: it's what I thought this would be useful for.
I think waht the 2nd loop does is:

from 1 to X, if there is a closing price for the stock, show it otherwise: move on to the next day.

the problem then is: it takes only the 1st ticker for reference...

am i right?

Bob Phillips
02-11-2008, 05:18 AM
I am not too sure what you mean. It seems to show an adjusted closing price if there is a closing price for all ten columns, except where the row is the first row.

I assume that PHResults2 etc. are range names, but I have no ide what the PriceHistory object is, so it is all a bit of conjecture.