Using S-PLUS Statistical Functions to Analyze Complex Financial Relationships in Excel
Introduction
The inter relationships of various instruments let sophisticated
market professionals implement many types of investment and trading
strategies. The focus of this article is to review the complex process
financial market professionals need undergo to understand and monitor
these sophisticated strategies. As a possible solution to this problem
we will discuss how to call functions from S-PLUS, a powerful
statistical analysis application, from within Microsoft Excel. For
example, to make a meaningful buy or sell decision of a foreign
equity, you need to:
- Evaluate the currency. - Is it going to increase or decrease in
value in relation to your home currency?
- Evaluate the market in which the equity trades. – How is the equity priced
in relation to a benchmark index?
- Evaluate other risk factors. - Some of these factors are market liquidity,
economic conditions and interest rate movements.
Many of these strategies are market neutral in nature. These trading positions focus on
taking advantage of pricing anomalies. Their profitability is not dependent upon the
market moving in a certain direction but upon a pricing equilibrium being restored.
In today’s fast changing markets, being able to find and take advantage of market inefficiencies
becomes a formidable task. In order to understand and analyze these complex dynamics, you need
to quantify these variables and see how they relate to each other.
- How does the price movement in one instrument affect the price movement in another
instrument?
- This analytical process needs to be ongoing. There may not have been a profitable
anomaly to take advantage of yesterday, but there is today.
The major data vendors can supply you with mountains of real-time and historical pricing data.
However to convert this data into meaningful information from which you can make a meaningful
trading decision, you need to perform further analysis. Having the data in Excel is a good
starting point. To perform the type of analysis needed to build a model, which will allow you
to quantify and understand patterns and complex relationships, you will most likely need
to access a statistical analysis application that has functions more powerful than
those available in Excel.
S-PLUS Forms A Direct Link to Excel
S-PLUS is a very powerful statistical application that provides the capability to perform a
high level of statistical analysis on financial information which has been retrieved into an Excel worksheet.
If you are going to work with S-PLUS, all you will need to do is select the range of the data you want
to analyze. You can then apply S-PLUS statistical functions and create S-PLUS models to
quantify the relationships you are analyzing.
The most attractive feature of integrating financial information with S-PLUS is that the data
stays in Excel. You do not need to spend time transferring the data and the results back and forth.
The results of the S-PLUS calculations can be placed into the cells of an Excel worksheet right
next to the range of financial information you are analyzing. You can easily call
S-PLUS functions from Excel.
The S-PLUS direct link to Excel becomes important when you realize that the major data vendors’
time series is retrieved into Excel as a DDE array. These array formulas, which may contain
information such as the open, high low, last and volume, can be self-updating. As the real-time
trading occurs, this new pricing information is added to the beginning of the time series while
the oldest pricing information, at the end of the time series, is dropped. Although the
financial information changes and updates, it remains in the same range of cells in
an Excel worksheet. This makes it easy to pass these ranges as parameters to any
S-PLUS functions. When the S-PLUS function calculates, the results are based on the
updated pricing information. The direct link to Excel simplifies the process of monitoring
a trade as variables change.
Using S-PLUS to Analyze a the Relationship of a Country Fund, a Currency and a Market Index
If you wanted to calculated the Net Asset Value of a closed-end country fund to see if a pricing
anomaly existed, you need to analyze the relationship between the price movement of the closed-end
country fund, the price movement of the country’s currency, and the price movement
of a market index, which represents the price movement of the fund’s holdings. To make
sense of this analysis:
- You would need to see how the price movements of these three variables are related.
- Also, how closely they are related.
- If there is a close relationship, you will then need to monitor the trading of
these three instruments, to see if a profitable pricing anomaly exists.
Performing a correlation analysis, and a standard deviation analysis lets you see how these
variables move in relation to each other. Doing a linear model regression analysis lets you
see a pricing anomaly.
We have created a workbook example, splusdemo.xls, which calls some S-PLUS functions and performs
several types of statistical analysis on some closed end country funds, their respective currencies
and stock indices. Download a sample spreadsheet. The workbook
contains historical pricing information from Bloomberg and some Visual Basic for Applications (VBA) macros
that call the appropriate S-PLUS functions to perform the calculations. The results of the S-PLUS
calculations are placed in an Excel worksheet and charts were created to display the results of the
S-PLUS functions.
Our spreadsheet models contain macros and DDE Links. For them to work properly we advise you
to download the model to a local drive and open it with Microsoft Excel rather than
opening it with you web browser.
The graph below displays an example of the linear regression analysis we performed on financial information
retrieved from Bloomberg into an Excel worksheet. The pricing data included a year’s worth of closing prices
for a country fund whose portfolio is composed of Japanese equities, the closing price for the Nikkei market
index and the average of the closing bid and asked for the Japanese Yen. We calculated a correlation between
the 3 variables and also calculated a standard deviation to see how closely they were related. We then
used a S-PLUS linear regression function to perform the analysis. We used Excel’s chart wizard to create
a chart to display the results shown below. However, S-PLUS has extensive graphing capabilities with
more choices and options than those which are available in Excel. You could just as easily created
this chart or several others with S-PLUS. All S-PLUS charts can be exported to Excel or any Windows
application.
Calling S-PLUS Functions from Excel
If you are accustomed and feel comfortable working with S-PLUS, you may find it easier to select
the data in an Excel worksheet and then switch to S-PLUS and work with the data. However, we chose
to stay in Excel and call the S-PLUS functions, have the functions perform the calculations and
place the results in the cells of an Excel workbook. In our case, we created a DDE Link to retrieve
the pricing information for a country fund, for its currency and its market index in the cells of
an Excel worksheet. We then created a VBA macro, which passed the ranges containing the data to
S-PLUS functions. For this example we used Excel 2000 (version 9.03821 SR-1), S-PLUS 2000
Professional (Release 3)
S-PLUS is constructed on an object model. Once you become familiar with what each object within
S-PLUS does, you can create macrocode, within Excel’s Visual Basic editor to access and manipulate
S-PLUS objects. The S-PLUS objects are similar to all objects in Windows-based applications in
that they have Properties and Methods that can be manipulated. The steps to programmatically call
an S-PLUS function in Excel are:
- Create an instance of the S-PLUS Application object. In VBA macrocode you dimension an object
variable then assign the S-PLUS Application to that variable. In our case we dimensioned an
object variable called oApp then assigned a reference to the S-PLUS application with the Visual Basic
Set statement.
- Create an instance of the S-PLUS Data Frame object. S-PLUS uses data frames as a matrix or a
table composed of rows and columns. They are very similar to tables and ranges found in Excel.
The Data Frame object is also created with a Visual Basic Set statement. Once the Data Frame
object is created, it can be assigned the data in a specific range in an Excel worksheet.
- Run an S-PLUS function. The specific S-PLUS function is run by calling its name and using
the ExecuteString method of the S-PLUS Application object. The Data Frame object, containing
the data and the function name are assigned to a string variable. The function performs the
calculations and the results are added to the Data Frame.
- Put the results contained in the Data Frame object into a variant array. Once the data is in
a variant array it can be easily put into the cells of an Excel worksheet.
Shown below is macrocode from a VBA procedure, which shows how to do all of the above steps
programmatically.
It calls the S-PLUS cor (correlation analysis) function. This procedure
is called by another procedure which also calls procedures which get
regression analysis and standard deviation from S-PLUS. The best way to learn how to call an S-PLUS function
is to download splusdemo.xls and review the macrocode. The macrocode is well documented and explains what each line
of code does.
|
Private Sub RunCorrelation(oApp As Object, rngData As Excel.Range, rngDest As Excel.Range) |
|
Dim oNewDataFrame As Object |
|
Dim sDataFrameName As String |
|
Dim sCorrelation As String |
|
Set oApp = CreateObject("S-PLUS.Application") |
|
sDataFrameName = "ExcelData" |
|
Set oNewDataFrame = oApp.CreateObject("DataFrame") |
|
.NewName = sDataFrameName |
|
sCorrelation = sDataFrameName & ".cor" |
|
oApp.ExecuteString sCorrelation & " <- cor(" & sDataFrameName & ")" |
|
Set oCorMatrix = oApp.GetObject("matrix", sCorrelation) |
|
vCorArray = oCorMatrix.DataAsArray |
|
.RemoveObject oNewDataFrame |
|
Set oNewDatFrame = Nothing |
About Insightful
- Insightful Corporation (NASDAQ: IFUL) provides enterprises with scalable data analysis solutions
that drive better decisions faster by revealing patterns, trends and relationships. The company is
a leading supplier of software and services for statistical data mining business analytics and
information retrieval enabling clients to gain information superiority.
- Insightful products include S-PLUS®, StatServer®, S-PLUS Analytic ServerT and VisiMineT. These
products add sophisticated data analysis into data warehousing and business intelligence initiatives.
Insightful consulting services provide specialized expertise and proven processes for the design,
development and deployment of customized analytical solutions. The company has been delivering
data analysis solutions for over a decade to thousands of companies in financial services,
pharmaceuticals, biotechnology, telecommunications, manufacturing, plus government
and research institutions.
- Headquartered in Seattle, Insightful has offices in New York City, Durham, North Carolina
and the U.K., with distributors around the world. For more information, visit www.insightful.com,
email info@insightful.com or call 206-283-8802
|
