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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

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
 

Forum statistics

Threads
1,136,649
Messages
5,676,990
Members
419,667
Latest member
MegEri

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