Simon Benninga. Financial Modelling 3-rd edition
Подождите немного. Документ загружается.
1088 Chapter 41
1089 Information from the Web
41.9 Summary
This chapter has covered the basics of the importation of data from the
Web into Excel. The automation of the import process using VBA can
include a myriad of applications. In the disk with this book we include
a fi le (fm3_chapter41_var_covar.xls) that shows a more sophisticated
application—the import of price data for the computation of the sample
variance-covariance matrix discussed in Chapter 10. The sky is the
limit.
Exercises
1. Create a query on the active worksheet to retrieve the current exchange rate from
U.S. dollars to British pounds. Hint: See http://fi nance.yahoo.com/currency or http://
uk.moneycentral.msn.com/investor/market/rates.asp for the rates.
2. Write macro to create the query in exercise 1. Hint: One way to do this might be
to record the macro, subsequently changing it to make it work better.
1090 Chapter 41
3. Write a macro that takes the value of the currently selected cell, converts it from
U.S. dollars to British pounds, and deposits the result as a value in the cell to the
right of the currently selected cell. You may assume that a worksheet called “temp”
in the current workbook can be used as a temporary storage space for the required
query. Hint: As exercise 2, recording the macro and changing the recorded result
might be the way to go.
4. Rewrite the macro from exercise 3. Use a new worksheet as a temporary storage
space for the required query and delete it before the macro exists. Check that
the input is a number. If the output cell is not empty, ask the user if it is OK to
overwrite it.
Appendix 1: Excerpts from the Help File
The content of this appendix is a lightly edited version of the Excel VBA
Help fi le, supplying technical information about objects and concepts
covered in this chapter.
IsNumeric Function
Returns a Boolean value indicating whether an expression can be evalu-
ated as a number.
Syntax
IsNumeric(expression)
The required expression argument is a Variant containing a numeric
expression or string expression.
Remarks
IsNumeric returns True if the entire expression is recognized as a number;
otherwise, it returns False. IsNumeric returns False if expression is a date
expression.
IsDate Function
Returns a Boolean value indicating whether an expression can be
converted to a date.
Syntax
IsDate(expression)
The required expression argument is a Variant containing a date
expression or string expression recognizable as a date or time.
1091 Information from the Web
Remarks
IsDate returns True if the expression is a date or is recognizable as a
valid date; otherwise, it returns False. In Microsoft Windows, the range
of valid dates is January 1, 100, through December 31, 9999; the ranges
vary among operating systems.
DateValue Function
Returns a Variant (Date).
Syntax
DateValue(date)
The required date argument is normally a string expression represent-
ing a date from January 1, 100, through December 31, 9999. However,
date can also be any expression that can represent a date, a time, or both
a date and time, in that range.
Remarks
If date is a string that includes only numbers separated by valid date
separators, DateValue recognizes the order for month, day, and year
according to the Short Date format you specifi ed for your system.
DateValue also recognizes unambiguous dates that contain month names,
in either long or abbreviated form. For example, in addition to recogniz-
ing 12/30/1991 and 12/30/91, DateValue also recognizes December 30,
1991, and Dec 30, 1991.
If the year part of date is omitted, DateValue uses the current year
from your computer’s system date. If the date argument includes time
information, DateValue doesn’t return it. However, if date includes
invalid time information (such as “89 : 98”), an error occurs.
DateSerial Function
Returns a Variant (Date) for a specifi ed year, month, and day.
Syntax
DateSerial(year, month, day)
The DateSerial function syntax has these named arguments:
1092 Chapter 41
Remarks
To specify a date, such as December 31, 1991, the range of numbers for
each DateSerial argument should be in the accepted range for the unit;
that is, 1–31 for days and 1–12 for months. However, you can also specify
relative dates for each argument using any numeric expression that rep-
resents some number of days, months, or years before or after a certain
date.The following example uses numeric expressions instead of absolute
date numbers. Here the DateSerial function returns a date that is the
day before the fi rst day (1 − 1), two months before August (8 − 2), and
10 years before 1990 (1990 − 10); in other words, May 31, 1980:
DateSerial(1990 − 10, 8 − 2, 1 − 1).
When any argument exceeds the accepted range for that argument, it
increments to the next larger unit as appropriate. For example, if you
specify 35 days, it is evaluated as one month and some number of days,
depending on where in the year it is applied. If any single argument is
outside the range −32,768 to 32,767, an error occurs. If the date specifi ed
by the three arguments falls outside the acceptable range of dates, an
error occurs.
Note For year, month, and day, if the Calendar property setting is
Gregorian, the supplied value is assumed to be Gregorian.
Day Function
Returns a Variant (Integer) specifying a whole number between 1 and
31, inclusive, representing the day of the month.
Syntax
Day(date)
The required date argument is any Variant, numeric expression, string
expression, or any combination, that can represent a date. If date con-
tains Null, Null is returned.
Part Description
Year Required; Integer. Number between 100 and 9999, inclusive, or a numeric
expression.
Month Required; Integer. Any numeric expression.
Day Required; Integer. Any numeric expression.
1093 Information from the Web
Month Function
Returns a Variant (Integer) specifying a whole number between 1 and
12, inclusive, representing the month of the year.
Syntax
Month(date)
The required date argument is any Variant, numeric expression, string
expression, or any combination, that can represent a date. If date con-
tains Null, Null is returned.
Year Function
Returns a Variant (Integer) containing a whole number representing the
year.
Syntax
Year(date)
The required date argument is any Variant, numeric expression, string
expression, or any combination, that can represent a date. If date con-
tains Null, Null is returned.
Appendix 2: The R1C1 Reference Style
You can also use a reference style where both the rows and the columns
on the worksheet are numbered. The R1C1 reference style is useful for
computing row and column positions in macros. In the R1C1 style, Excel
indicates the location of a cell with an R followed by a row number and
a C followed by a column number.
Reference Meaning
R[−2]C
A relative reference to the cell two rows up and in the same column
R[2]C[2] A relative reference to the cell two rows down and two columns to the
right
R2C2 An absolute reference to the cell in the second row and in the second
column
R[−1]
A relative reference to the entire row above the active cell
R An absolute reference to the current row
1094 Chapter 41
When you record a macro, Excel records some commands by using
the R1C1 reference style. For example, if you record a command such
as clicking the AutoSum button to insert a formula that adds a range of
cells, Excel records the formula by using R1C1-style, not A1-style,
references.
To turn R1C1 reference style on or off,
Click Options on the Tools menu, and then click the General tab.
Under Settings, select or clear the R1C1 reference style check box.
Selected References
This bibliography is not intended to be extensive. We give the references
cited in Financial Modeling, and we also include a limited number of
books and articles that may help readers expand their horizons to more
advanced topics. On occasion the same reference appears several times
in different sections.
Chapters 1–5: Corporate Finance and Valuation
Benninga, S., and O. Sarig. 2003. Risk, Returns, and Values in the Presence of Differential
Taxation. Journal of Banking and Finance, 27.
Benninga, S. Z., and O. H. Sarig. 1997. Corporate Finance: A Valuation Approach.
McGraw-Hill.
Brealey, R. A., S. C. Myers, and F. Allen. 2005. Principles of Corporate Finance, 8th ed.
McGraw-Hill.
Dittmar, A. K., and R. F. Dittmar. 2004. Stock Repurchase Waves: An Explanation of the
Trends in Aggregate Corporate Payout Policy. Working paper, University of Michigan.
Gordon, M. J. 1959. Dividends, Earnings, and Stock Prices. Review of Economics and
Statistics, 41.
McKinsey and Company, T. Koller, M. Goedhart, and D. Wessels, 2005. Valuation: Measur-
ing and Managing the Value of Companies, 4th ed. Wiley.
Ross, S. A., R. W. Westerfi eld, and J. Jaffe. 2004. Corporate Finance, 7th ed.
McGraw-Hill/Irwin.
Chapters 6–7: Leasing
Abdel-Khalik, R. 1981. Economic Effects on Lessees of FASB. Statement No. 13, Account-
ing for Leases. Stamford, CT: Financial Accounting Standards Board.
Copeland, T. E., and J. F. Weston. 1982. A Note on the Evaluation of Cancelable Operating
Leases. Financial Management, 11.
El-Gazzar, S., S. Lilien, and V. Pastena. 1986. Accounting for Leases by Lessees. Journal of
Accounting and Economics, 8.
Financial Accounting Standards Board, 1976. Statement No. 13, Accounting for Leases.
Stamford, CT.
Franks, J. R., and S. D. Hodges. 1978. Valuation of Financial Lease Contracts: A Note.
Journal of Finance, 33.
Levy, H., and M. Sarnat. 1979. On Leasing, Borrowing, and Financial Risk. Financial Man-
agement, winter.
Lewellen, W. G., M. S. Long, and J. J. McConnell. 1979. Asset Leasing in Competitive Capital
Markets. Journal of Finance, 31.
McConnell, J. J., and J. S. Schallheim. 1983. Valuation of Asset Leasing Contracts. Journal
of Financial Economics, 12.
Miller, M. H., and C. W. Upton. 1976. Leasing, Buying, and the Cost of Capital Services.
Journal of Finance, 31.
1096 Selected References
Myers, S. C., D. A. Dill, and A. J. Bautista. 1976. Valuation of Financial Lease Contracts.
Journal of Finance, 31.
Nakayama, M., S. Lilien, and M. Benis. 1981. Due Process and FAS No. 13. Management
Accounting, 52.
Ofer, A. R. 1976. The Evaluation of the Lease versus Purchase Alternative. Financial Man-
agement, 5.
Schallheim, James S. 1994. Lease or Buy? Principles for Sound Decision Making, Harvard
Business School.
Chapters 8–12: Portfolio Basics
Bengtsson, C., and J. Holst. 2002. On Portfolio Selection: Improved Covariance Matrix
Estimation for Swedish Asset Returns. Working paper, Lund University and Lund Institute
of Technology.
Black, F. 1972. Capital Market Equilibrium with Restricted Borrowing. Journal of
Business, 45.
Bodie, Z., A. Kane, and A. J. Marcus. 2004, Investments, 6th ed. McGraw-Hill/Irwin.
Chan, L., J. Karceski, and J. Lakonishok. 1999. On Portfolio Estimation: Forecasting Covari-
ances and Choosing the Risk Model. Review of Financial Studies, 12.
Disatnik, D. J., and S. Benninga. 2007. Shrinking the Covariance Matrix: Simpler Is Better.
Journal of Portfolio Management, 33.
Efron, B., and C. Morris. 1977. Stein’s Paradox in Statistics. Scientifi c American, 236.
Elton, E. J., M. J. Gruber, and T. Ulrich. 1978. Are Betas Best? Journal of Finance, 23.
Elton, E. J., M. J. Gruber, S. J. Brown, and W. N. Goetzmann. 2004. Modern Portfolio Theory
and Investment Analysis, 6th ed. Wiley.
Fama, E., and K. French. 1997. Industry Costs of Equity. Journal of Financial Eco-
nomics, 43.
Frost, P., and J. Savarino. 1986. An Empirical Bayes Approach to Effi cient Portfolio Selec-
tion. Journal of Financial and Quantitative Analysis, 21.
D’Avolio, G. 2002. The Market for Borrowing Stock. Journal of Financial Economics, 66.
Green, R., and B. Hollifi eld. 1992. When Will Mean-Variance Effi cient Portfolios Be Well
Diversifi ed? Journal of Finance, 47.
Green, R. C. 1986. Positively Weighted Portfolios on the Minimum-Variance Frontier.
Journal of Finance, 41.
Haugen, R. A. 1997. Modern Investment Theory. Prentice Hall.
Jagannathan, R., and T. Ma. 2003. Risk Reduction in Large Portfolios: Why Imposing the
Wrong Constraints Helps. Journal of Finance, 44.
Jensen, M. C., ed., 1972. Studies in the Theory of Capital Markets. New York: Praeger.
Jobson, J., and B. Korkie. 1981. Putting Markovitz Theory to Work. Journal of Portfolio
Management, 7.
Jorion, P. 1986. Bayes-Stein Estimation of Portfolio Analysis. Journal of Financial and
Quantitative Analysis, 213.
Ledoit, O., and M. Wolf. 2003. Improved Estimation of the Covariance Matrix of
Stock Returns with an Application to Portfolio Selection. Journal of Empirical Fi-
nance, 10.
1097 Selected References
Ledoit, O., and M. Wolf. 2004. A Well-Conditioned Estimator for Large-Dimensional Cova-
riance Matrices. Journal of Multivariate Analysis, 88.
Ledoit, O., and M. Wolf. 2004. Honey, I Shrunk the Sample Covariance Matrix. Journal of
Portfolio Management, 31.
Lintner, J. 1965. The Valuation of Risky Assets and the Selection of Risky Investments in
Stock Portfolios and Capital Budgets. Review of Economics and Statistics, 47.
Malkiel, B. G. 2005. Refl ections on the Effi cient Market Hypothesis: 30 Years Later. Finan-
cial Review, 40.
Markowitz, H. M. 1952. Portfolio selection. Journal of Finance, 7.
Merton, R. C. 1973. An Analytic Derivation of the Effi cient Portfolio Frontier. Journal of
Financial and Quantitative Analysis, 7.
Michaud, R. 1989. The Markowitz Optimization Enigma: Is Optimized Optimal? Financial
Analysts Journal, 45.
Michaud, R. 1998. Effi cient Asset Management: A Practical Guide to Stock Portfolio Opti-
mization and Asset Allocation. Boston: Harvard Business School Press.
Mossin, J. 1966. Equilibrium in a Capital Market. Econometrica, 34.
Nielsen, L. T. 1987. Positively Weighted Frontier Portfolios: A Note. Journal of
Finance, 42.
Press, W. H., B. P. Flannery, S. A. Teukolsky, and W. T. Vetterling. 1986. Numerical Recipes:
The Art of Scientifi c Computing. Cambridge University Press.
Roll, R. 1977. A Critique of the Asset Pricing Theory’s Tests, Part I: On Past and Potential
Testability of the Theory. Journal of Financial Economics, 4.
Roll, R. 1978. Ambiguity When Performance Is Measured by the Securities Market Line.
Journal of Finance, 33.
Sharpe, W. F. 1963. A Simplifi ed Model for Portfolio Analysis. Management Science, 9.
Sharpe, W. F. 1964. Capital Asset Prices: A Theory of Market Equilibrium under Conditions
of Risk. Journal of Finance, 19.
Stein, C. 1955. Inadmissibility of the Usual Estimator for the Mean of a Multivariate
Normal Distribution. Proceedings of the Third Berkeley Symposium on Probability and
Statistics. Berkeley: University of California Press.
Surowiecki, J. 2003. Get Shorty. New Yorker magazine. http://newyorker.com/talk/content/
?031201ta_talk_surowiecki .
Wolf, M. 2004. Resampling vs. Shrinkage for Benchmarked Managers. Working paper,
Department of Economics and Business, Universitat Pompeu Fabra.
Chapter 13: Black-Litterman
Best, M. J., and R. R. Grauer. 1985. Capital Asset Pricing Compatible with Observed
Market Value Weights. Journal of Finance, 40.
Best, M. J., and R. R. Grauer. 1991. On the Sensitivity of Mean-Variance-Effi cient Portfolios
to Changes in Asset Means: Some Analytical and Computational Results. Review of Finan-
cial Studies, 2.
Bevan, A., and K. Winkelmann. 1998. Using the Black-Litterman Global Asset Allocation
Model: Three Years of Practical Experience. Goldman Sachs.
Black, F., and R. Litterman. 1991. Global Asset Allocation with Equities, Bonds, and Cur-
rencies. Goldman Sachs.