LOST!

allenD

New Member
Joined
Sep 9, 2006
Messages
5
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
Joined
Aug 21, 2004
Messages
16,995
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top