# LOST!

#### allenD

##### New Member
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

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### jindon

##### MrExcel MVP
Not sure if this is what you wanted...
Code:
``````Sub sp500()
Dim a, i As Long b(1 to 5000, 1 To 1), ws As Worksheet

Set ws = ActiveSheet
ws.Range("h3:h5000").Clear

a = Workbooks("sp500.xls").Sheets("prices").Range("e3:e5000").Value
For i = 1 To UBound(a,1) - 17
b(i,1) = Abs((a(i,1) - a(i+17,1)) / a(i+17,1))
Next

ws.Range("h3").Resize(i-1).Value = b
Set ws = Nothing
End Sub``````

Replies
12
Views
318
Replies
0
Views
156
Replies
17
Views
535
Replies
5
Views
191
Replies
0
Views
119

1,186,326
Messages
5,957,217
Members
438,293
Latest member
ginkycart

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back