I'm trying to access historical stock data from another workbook("sp500.xls") and calculate the percentage change over the last 17 days. the variable curPrice is todays price which is located Workbook("sp500.xls").Sheets("prices") in cell "e3". The variable lookBackPrice is the price 17 days ago which is also located in the same workbook adn sheet but in cell "e20". I have 5000 prices in this workbook in range("e3:e5000"). I want to calculate the percentage change for each price over a 17day period and store the percentage changes in the range("H3:H4937).
This is the code that I wrote to "trying" to accomplish this.
Please show me the correct way to write this and also point out to me what I did wrong so I will understand whats going on.
THANKS!
Allen
Sub sp500()
Range("h3:h5000").Select
Selection.Clear
Dim curPrice
Dim lookBackPrice
Dim percentChg
Workbooks("sp500.xls").Sheets("prices").Range("e3").Value = curPrice
Workbooks("sp500.xls").Sheets("prices").Range("e20").Value = lookBackPrice
percentChg = "= Abs((curPrice - lookBackPrice) / (lookBackPrice))"
Range("h3").Value = percentChg
Range("h3").Select
Selection.AutoFill Destination:=Range("h3:h5000"), Type:=xlFillDefault
End Sub
This is the code that I wrote to "trying" to accomplish this.
Please show me the correct way to write this and also point out to me what I did wrong so I will understand whats going on.
THANKS!
Allen
Sub sp500()
Range("h3:h5000").Select
Selection.Clear
Dim curPrice
Dim lookBackPrice
Dim percentChg
Workbooks("sp500.xls").Sheets("prices").Range("e3").Value = curPrice
Workbooks("sp500.xls").Sheets("prices").Range("e20").Value = lookBackPrice
percentChg = "= Abs((curPrice - lookBackPrice) / (lookBackPrice))"
Range("h3").Value = percentChg
Range("h3").Select
Selection.AutoFill Destination:=Range("h3:h5000"), Type:=xlFillDefault
End Sub