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 |
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 |