PDA

View Full Version : comparing dates in vba



choubix
02-11-2008, 09:27 AM
hello,

I'd like to build a Black/Litterman portofolio in excel.
I need to download data series for the stocks I want to use in the model.

the thing is: the stocks are traded on different stock exchanges meaning that the trading days are different.

since I'll have to build a correlation matrix the data series have to be of equal length.

how can I compare, using VBA, whether a date is a trading day for all the stocks I would like to use in my spreadsheet?

I have this code that I now understand (thanks XLD) that I think I could tweak.

what it returns:
column 1 : the trading dates for the 1 stock in the list
columns 2 to i: the adjusted closing prices on these dates for the stocks in the list

no problem there when the stocks are all trading on the same exchange.

but there are discrepencies when the stock are on different exchanges.


any idea on how I can compare the trading dates for stocks on different stock exchanges within a range please?



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

thanks!

Bob Phillips
02-11-2008, 09:32 AM
It would probably be better if you could post a sample workbook with the data that you have, and what you want to get.