Skip to main content

2016 | Buch

Essentials of Excel, Excel VBA, SAS and Minitab for Statistical and Financial Analyses

insite
SUCHEN

Über dieses Buch

This introductory textbook for business statistics teaches statistical analysis and research methods via business case studies and financial data using Excel, MINITAB, and SAS. Every chapter in this textbook engages the reader with data of individual stock, stock indices, options, and futures.

One studies and uses statistics to learn how to study, analyze, and understand a data set of particular interest. Some of the more popular statistical programs that have been developed to use statistical and computational methods to analyze data sets are SAS, SPSS, and MINITAB.

Of those, we look at MINITAB and SAS in this textbook. One of the main reasons to use MINITAB is that it is the easiest to use among the popular statistical programs. We look at SAS because it is the leading statistical package used in industry. We also utilize the much less costly and ubiquitous Microsoft Excel to do statistical analysis, as the benefits of Excel have become widely recognized in the academic world and its analytical capabilities extend to about 90 percent of statistical analysis done in the business world. We demonstrate much of our statistical analysis using Excel and double check the analysis and outcomes using MINITAB and SAS – also helpful in some analytical methods not possible or practical to do in Excel.

Inhaltsverzeichnis

Frontmatter

Statistical Analysis

Frontmatter
Chapter 1. Introduction

A person studies and uses to learn how to study, analyze, and understand a data set of particular interest. But one problem in studying statistics is that it is a very intense computational discipline. We can get a sense of the computational intensity of statistics by looking at the average statistical formula and the correlation coefficient statistical formula shown below. x¯=∑i=1nxinr=1n−1∑i=1nxi−x¯yi−y¯1n−1∑i=1nxi−x¯21/21n−1∑i=1nyi−y¯21/2AverageCorrelationcoefficient$$ \begin{array}{ll}\overline{x}=\frac{{\displaystyle \sum_{i=1}^n{x}_i}}{n}\hfill & r=\frac{\frac{1}{n-1}{\displaystyle \sum_{i=1}^n\left({x}_i-\overline{x}\right)\left({y}_i-\overline{y}\right)}}{{\left[\frac{1}{n-1}{\displaystyle \sum_{i=1}^n{\left({x}_i-\overline{x}\right)}^2}\right]}^{1/2}{\left[\frac{1}{n-1}{\displaystyle \sum_{i=1}^n{\left({y}_i-\overline{y}\right)}^2}\right]}^{1/2}}\hfill \\ {}\mathrm{Average}\hfill & \mathrm{Correlation}\;\mathrm{coefficient}\hfill \end{array} $$The average formula looks fairly simple, but it becomes more and more difficult as n increases. The correlation coefficient formula is obviously more complex and tedious to perform—and other computational formulas are even more complicated. Therefore one of the biggest costs in both time and money in studying and using statistics is the implementation costs of implementing statistical concepts on data sets that we are interested in studying, analyzing, and understanding.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 2. Data Collection, Presentation, and Yahoo Finance

Statistics is mainly associated with numbers. A complete statistical analysis of a data set consists of both numbers and graphs. Graphs many times allow users to understand a data set that is very hard to capture with number.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 3. Histograms and the Rate of Returns of JPM and JNJ

In this chapter we are interested in the dispersion and the general location of JPM’s annual returns. In this chapter we will use histograms to analyze the dispersion and location of JPM’s annual stock price returns.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 4. Numerical Summary Measures on Rate of Returns of Amazon, Walmart, and the S&P 500

In this chapter we will calculate numeric summary measures on the rate of return of the S&P 500, Amazon, and Walmart. Statistical numerical numbers look at data in five different ways. The five different ways are measure of central location, measure of variability, measure of relative standing, measure of share, and measure of linear relationship. We will look at the weekly, monthly, and annual rate of return from 2008 to 2015. 2008 was the year of the great recession. Since 2008 we have seen the market improve tremendously.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 5. Probability Concepts and Their Analysis

In the previous chapters we studied on how to analyze and describe a data set. It was implied that we had all the data items that we need. We will now deal with the situations where this is not the case. We will be unable to get all the data items that we are interested in analyzing because of prohibitive costs. Cost could be time, money, or effort.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 6. Discrete Random Variables and Probability Distributions

In this chapter and the next three chapters, we will begin to study specific data sets of interest in statistics. With all of these data sets, we are interested in how the data items are distributed and what is the likelihood of getting a specific data item from a data set.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 7. The Normal and Lognormal Distributions

In the last chapter, we examined the probability distribution of discrete random variables. In this chapter, we will look at the probability distribution of random variables.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 8. Sampling Distributions and Central Limit Theorem

Many times it is impossible or too costly to analyze the population data. Because of this we are only able to analyze a sample from the population. After analyzing the sample data, are we able to understand the population data from the sample data? The answer is yes. In this chapter we will study why the answer is yes.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 9. Other Continuous Distributions

We will now look at other continuous distributions that are commonly used in statistics. It is important to study all these distributions because later we will make statistical inferences based on the particular distribution we are working with. We should remember that the area under the density curve of each of these distributions is equal to 1.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 10. Estimation

In this chapter we use the continuous variables that we studied in Chaps 7 and 9 to estimate the mean, variance, and proportion of populations.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 11. Hypothesis Testing

In the last chapter, we made inferences about a population parameter by creating a confidence interval from a sample. We will now look at another method, called hypothesis testing, for making inferences about a population parameter. In hypothesis testing we infer that the stated null hypothesis (H0) is true until there is convincing but not perfect evidence that the null hypothesis is not true. Our evidence is from the sample that we obtain. We conclude that there is convincing evidence when the p-value is less than the alpha value. The p-value will be discussed later in this chapter.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 12. Analysis of Variance and Chi-Square Tests

Often in statistics we are interested in whether two or more samples have the same mean from the same population. Statistics uses the concept of analysis of variance to answer this question.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 13. Simple Linear Regression and the Correlation Coefficient

In the previous chapters, we have been primarily interested in analyzing single variables. In this chapter, we are interested in analyzing two variables and the relationship between them. The two techniques that we will look at are regression analysis and correlation analysis.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 14. Simple Linear Regression and Correlation: Analyses and Applications

When we do regression analysis, there are five underlying assumptions:

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 15. Multiple Linear Regression

In the previous two chapters, we examined the relationship between two variables. In this chapter we will look at the relationship among three or more variables, using regression analysis.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 16. Residual and Regression Assumption Analysis

When we do regression analysis, there are six underlying assumptions:

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 17. Nonparametric Statistics

There are three types of statistical data. The three types of statistical data are numerical, categorical, and ordinal. Numerical data is used for measurement, for example, the height of a person and miles to New York City. You can make mathematical operations on numerical data and the resulting number has meaning. Categorical data represents characteristics, for example, male or female and true or false. Ordinal data has ranking. For example, an experience could be poor, fair, and excellent. In this chapter we will do statistical tests on ordinal data.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 18. Time Series: Analysis, Model, and Forecasting

In statistics there are two kinds of data, cross-section data and time-series data. Time-series data are those recorded over time. Cross-section data pertain to a particular time. In this chapter we will look at specific issues of time-series data.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 19. Index Numbers and Stock Market Indexes

In this chapter we will look at indexes. Index numbers are numbers that compare an activity in one time or place to a similar activity in a specific base period or place. The first index that we will look at is the simple price index of a single item. When used this way, we can measure the relative price change over time.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 20. Sampling Surveys: Methods and Applications

In previous chapters we investigated sampling but only in terms of simple random sampling, in which each potential sample of n members has an equal chance of being chosen. Most of the time, this requirement is satisfied due to the fact that the sample size is small compared to the population. But when the sample size becomes a large part of the population, some adjustments must be made.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 21. Statistical Decision Theory

This chapter was originally in Lee et al. (2013). It discusses the statistical decision theory. The main topics covered in this chapter are (1) Four Key Elements of a Decision, (2) Decisions Based on Extreme Values, (3) Expected Monetary Value and Utility Analysis, (4) Bayes Strategies, (5) Decision Trees and Expected Monetary Values, (6) Mean and Variance Trade-Off Analysis (Optional), and (7) The Mean and Variance Method for Capital Budgeting Decisions.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai

Advanced Applications of Microsoft Excel Programs in Financial Analysis

Frontmatter
Chapter 22. Introduction to Excel Programming

A lot of the work done by an Excel user is repetitive and time consuming. Fortunately for an Excel user, Excel offers a powerful and professional programming language and powerful and professional programming environment to automate a big portion of their work. This book has illustrated some of the things that can be accomplished by Excel’s programming language called Visual Basic for Applications or more commonly known as VBA.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 23. Introduction to VBA Programming

In the previous chapter, we mentioned that VBA was Excel’s programming language. It turns out that VBA is the programming language for all the Microsoft Office applications. Also VBA is becoming the programming language of other popular applications. Some of the other applications that are using VBA as its programming language are Visio and Business Objects. This is very exciting news for people who automate applications. The main advantage is that a person does not have to constantly learn a new language when he or she deals with a new application. Another important advantage of an application adopting VBA as a programming language is it is a language that uses most of the programming concepts that professional programmers use; therefore, VBA is a powerful language. Now, it is easier for an Excel programmer to do Access VBA programming, Word VBA, or even PowerPoint VBA programming. The finance people would say that there is a return in the investment of time in learning VBA.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 24. Professional Techniques Used in Excel and Excel VBA Techniques

In this chapter we will discuss Excel and Excel VBA techniques that I find useful and are not usually discussed or pointed out in Excel and Excel VBA books. These techniques come from my experience as an Excel professional.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 25. Binomial Option Pricing Model Decision Tree Approach

Microsoft Excel is one of the most powerful and valuable tools available to the business users. The financial industry in New York City has recognized this value. We can see this by going to one of the many job sites on the Internet. Two Internet sites that demonstrate the value of someone who knows Microsoft Excel very well are www.dice.com and www.indeed.com. For both of these Internet sites, search for New York City and VBA, which is Microsoft Excel’s programming language, and you will see many job postings requiring VBA.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 26. Microsoft Excel Approach to Estimating Alternative Option Pricing Models

This chapter shows how Microsoft Excel can be used to estimate call and put options for (a) Black–Scholes model for individual stock, (b) Black–Scholes model for stock indices, and (c) Black–Scholes model for currencies. In addition, we are going to present how an Excel program can be used to estimate American Options. Section 26.2 presents an option pricing model for Individual Stocks, Sect. 26.3 presents an option pricing model for Stock Indices, Sect. 26.4 presents option pricing model for Currencies, Sect. 26.5 presents Bivariate Normal Distribution Approach to calculate American Call Options, Sect. 26.6 presents the Black’s approximation method to calculate American Call Options, Sect. 26.7 presents how to evaluate American Call option when dividend yield is known, and Sect. 26.8 summarizes this chapter. Appendix 26.1 defines the Bivariate Normal probability density function, and Appendix 26.2 presents the Excel program to calculate the American call option when dividend payments are known.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 27. Alternative Methods to Estimate Implied Variance

In this chapter we will introduce how to use Excel to estimate implied volatility. First, we use approximate linear function to derive the volatility implied by Black–Merton–Scholes model. Second, we use nonlinear method, which includes goal seek and bisection method, to calculate implied volatility. Third, we demonstrate how to get the volatility smile using IBM data. Fourth, we introduce constant elasticity volatility (CEV) model and use bisection method to calculate the implied volatility of CEV model. Finally, we calculate the 52 weeks historical volatility of a stock. We used the Excel function webservice to retrieve the 52 historical stock prices.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 28. Greek Letters and Portfolio Insurance

In Chap. 26, we have discussed how the call option value can be affected by stock price per share, exercise price per share, the contract period of the option, the risk-free rate, and the volatility of the stock return. In this chapter, we will mathematically analyze these kinds of relationships. Parts of these mathematical relationships are called “Greek letters” by finance professionals. Here we specifically derive Greek letters for call (put) options on non-dividend stock and dividend-paying stock. Some examples will be provided to explain applications of these Greek letters. Sections 28.1–28.5 discuss the formula, Excel function, and applications of delta, theta, gamma, vega, and rho, respectively. Section 28.6 derives the partial derivative of stock options with respect to their exercise prices. Section 28.7 describes the relationship between delta, theta, and gamma and their implication in delta-neutral portfolio. Section 28.8 presents a portfolio insurance example. Finally in Sect. 28.9, we summarize and conclude this chapter.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 29. Portfolio Analysis and Option Strategies

The main purposes of this chapter are to show how Excel programs can be used to perform portfolio selection decisions and to construct option strategies. In Sect. 29.2, we demonstrate how Microsoft Excel can be used to inverse the matrix. In Sect. 29.3, we discuss how Excel programs can be used to estimate the Markowitz portfolio models. In Sect. 29.4, we discuss alternative option strategies. In Sect. 29.5, we summarize the results of this chapter.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 30. Simulation and Its Application

In this chapter, we will introduce Monte Carlo simulation which is a problem-solving technique. This technique can approximate the probability of certain outcomes by using random variables, called simulations. Monte Carlo simulation is named after the city in Monaco. The primary attractions in this place are casinos having gambling games, like dice, roulette, and slot machines. In these games of chance, there exist random behavior.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai

Applications of SAS Programs to Financial Analysis

Frontmatter
Chapter 31. Application of Simultaneous Equation in Finance Research: Methods and Empirical Results

Based upon the paper by Chen and Lee (2010) and Lee et al. (2015), we will first discuss the development of 2SLS, 3SLS, and generalized method of moments (GMM). Then, we will use GE as an example to show how SAS program can be used to estimate simultaneous equations system in terms of 2SLS, 3SLS, and GMM. In the second section, we will develop the model specification for 2SLS, 3SLS, and GMM. In the third section, we will discuss the simultaneous equation system for investment policy, financing policy, and dividend policy. In the fourth section, we will use GE data to show how simultaneous equation system can be estimated by 2SLS, 3SLS, and GMM in terms of SAS program. The GE data is presented in Appendix 1. The SAS program used to estimate these different methods will be presented in Appendix 2. Finally, in the fifth section, we summarize the results.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Chapter 32. Hedge Ratios: Theory and Applications

One of the best uses of derivative securities such as futures contracts is in hedging. In the past, both academicians and practitioners have shown great interest in the issue of hedging with futures. This is quite evident from the large number of articles written in this area.

Cheng-Few Lee, John Lee, Jow-Ran Chang, Tzu Tai
Backmatter
Metadaten
Titel
Essentials of Excel, Excel VBA, SAS and Minitab for Statistical and Financial Analyses
verfasst von
Cheng-Few Lee
John Lee
Jow-Ran Chang
Tzu Tai
Copyright-Jahr
2016
Electronic ISBN
978-3-319-38867-0
Print ISBN
978-3-319-38865-6
DOI
https://doi.org/10.1007/978-3-319-38867-0