Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection of range for the 'slope' function
Hello everyone,
Here is what I'm trying to do: 1) I have column labels, and row labels on one worksheet which I input into a function. 2) These inputs should be matched with column and row labels, and then input into the index function to get a certain cell - let's call it the event cell. I'm ok with this part. Then: 3) I would like to collect a one column array. The number of cells in this array should depend on an input from a cell in the worksheet, and the final cell in the array should be the column label of the column where I am inputing the formula. 4) The array from 3) should go into the slope function. 5) Then I want to get the second array for the slope function from a fixed column, but the same rows as in the variable first array, and this should input into the function. Below is what I have been trying to do, but it's not working. I would appreciate any help. Option Base 1 Function beta(company_name, event_date, event_window) 'Calculates beta based on pre-event window data Dim row_num, col_num, col_mar, event_cell, market_cell 'Event Cell Location Dim L As Integer 'Estimation window Dim i As Integer Dim start_equity, start_market 'start of array Dim y(), x() As Variant 'Equity Array, Market Array for regression beta = Application.WorksheetFunction.Slope(y(company_name , event_date, event_window), x(company_name, event_date, event_window)) End Function Function event_cell(company_name, event_date, event_window) 'Location of Event Cell row_num = Application.WorksheetFunction.Match(event_date, Sheets("Equity Returns").Range("ER_Dates"), 0) col_num = Application.WorksheetFunction.Match(company_name, Sheets("Equity Returns").Range("Companies"), 0) event_cell = Application.WorksheetFunction.Index(Sheets("Equity Returns").Range("Equity_Returns"), row_num, col_num) End Function Function market_cell(company_name, event_date, event_window) 'Location of Market Cell row_num = Application.WorksheetFunction.Match(event_date, Sheets("Equity Returns").Range("ER_Dates"), 0) col_mar = Application.WorksheetFunction.Match("FTSE All Share", Sheets("Equity Returns").Range("Companies"), 0) market_cell = Application.WorksheetFunction.Index(Sheets("Equity Returns").Range("Equity_Returns"), row_num, col_mar) End Function Function y(company_name, event_date, event_window) 'Definition of Estimation Window for Equity Dim equity_array() As Variant L = Sheets("Abnormal Returns").Range("B4") ReDim equity_array(L) start_equity = event_cell(company_name, event_date, event_window).Offset(-L + event_window, 0) 'Array Generation For i = 0 To L equity_array = start_equity.Offset(i, 0) Next i y = equity_array End Function Function x(company_name, event_date, event_window) 'Definition of Estimation Window for Equity Dim market_array() As Variant L = Sheets("Abnormal Returns").Range("B4") ReDim market_array(L) start_market = market_cell(company_name, event_date, event_window).Offset(-L + event_window, 0) 'Array Generation For i = 0 To L market_array = start_equity.Offset(i, 0) Next i x = equity_array End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection of range for the 'slope' function
Par t of you r problem may be the referencing of ranges on worksheets without
passing the parameter to the function from Function x(company_name, event_date, event_window) 'Definition of Estimation Window for Equity Dim market_array() As Variant L = Sheets("Abnormal Returns").Range("B4") ReDim market_array(L) to Function x(company_name, event_date, event_window,L) 'Definition of Estimation Window for Equity Dim market_array() As Variant ReDim market_array(L) Excel won't call the function is you change the value in Sheets("Abnormal Returns").Range("B4") without referencing the the cell in the call paramters. For debugging the code you should add break points in the functions by clicking on the line in the code and then pressing F9. Then go back to worksheet and force a change in the worksheet to call the functions. I do this by clicking on the cell in the worksheet where the call to the function is located (ie =Function x()). then going to the Fx box at top of worksheet and clicking at end of formula with mouse and pressing the ENTER on the keyboard. You can step through the code by typing F8. You can view values by adding watch to VBA. Hightlight variable market_array with mouse then right click and select ADD WATCH. "avk47" wrote: Hello everyone, Here is what I'm trying to do: 1) I have column labels, and row labels on one worksheet which I input into a function. 2) These inputs should be matched with column and row labels, and then input into the index function to get a certain cell - let's call it the event cell. I'm ok with this part. Then: 3) I would like to collect a one column array. The number of cells in this array should depend on an input from a cell in the worksheet, and the final cell in the array should be the column label of the column where I am inputing the formula. 4) The array from 3) should go into the slope function. 5) Then I want to get the second array for the slope function from a fixed column, but the same rows as in the variable first array, and this should input into the function. Below is what I have been trying to do, but it's not working. I would appreciate any help. Option Base 1 Function beta(company_name, event_date, event_window) 'Calculates beta based on pre-event window data Dim row_num, col_num, col_mar, event_cell, market_cell 'Event Cell Location Dim L As Integer 'Estimation window Dim i As Integer Dim start_equity, start_market 'start of array Dim y(), x() As Variant 'Equity Array, Market Array for regression beta = Application.WorksheetFunction.Slope(y(company_name , event_date, event_window), x(company_name, event_date, event_window)) End Function Function event_cell(company_name, event_date, event_window) 'Location of Event Cell row_num = Application.WorksheetFunction.Match(event_date, Sheets("Equity Returns").Range("ER_Dates"), 0) col_num = Application.WorksheetFunction.Match(company_name, Sheets("Equity Returns").Range("Companies"), 0) event_cell = Application.WorksheetFunction.Index(Sheets("Equity Returns").Range("Equity_Returns"), row_num, col_num) End Function Function market_cell(company_name, event_date, event_window) 'Location of Market Cell row_num = Application.WorksheetFunction.Match(event_date, Sheets("Equity Returns").Range("ER_Dates"), 0) col_mar = Application.WorksheetFunction.Match("FTSE All Share", Sheets("Equity Returns").Range("Companies"), 0) market_cell = Application.WorksheetFunction.Index(Sheets("Equity Returns").Range("Equity_Returns"), row_num, col_mar) End Function Function y(company_name, event_date, event_window) 'Definition of Estimation Window for Equity Dim equity_array() As Variant L = Sheets("Abnormal Returns").Range("B4") ReDim equity_array(L) start_equity = event_cell(company_name, event_date, event_window).Offset(-L + event_window, 0) 'Array Generation For i = 0 To L equity_array = start_equity.Offset(i, 0) Next i y = equity_array End Function Function x(company_name, event_date, event_window) 'Definition of Estimation Window for Equity Dim market_array() As Variant L = Sheets("Abnormal Returns").Range("B4") ReDim market_array(L) start_market = market_cell(company_name, event_date, event_window).Offset(-L + event_window, 0) 'Array Generation For i = 0 To L market_array = start_equity.Offset(i, 0) Next i x = equity_array End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using the slope function with non contiguous cells | Excel Programming | |||
using slope function with non contiguous cells | Excel Worksheet Functions | |||
Anomolous behaviour of slope function. | Excel Worksheet Functions | |||
how can i get the slope function to ignore missing data? | Excel Discussion (Misc queries) | |||
Function to invert Range-selection | Excel Programming |