Dr. Mayes' Excel Files
Dr. Mayes' Excel Files |
|||||||
|
|
|
|
|
|
|
|
This page is where I'll post my Excel files that I think are generally useful, rather than those that are specific to a particular class. If you find any errors or have questions about these files, please e-mail me.
Macro Warning: Some of these files make use of macros, and will not function properly if you do not allow the macros to run. You may need to go to Tools Macros Security and set the security level to Medium. This security setting will prompt you to either allow macros to run or not.
Investments Related Workbooks |
|
|
(beta version) |
This Excel add-in retrieves stock, mutual fund, and option quotes from Yahoo! Finance. Download the add-in and then go to Tools -->Add-ins and then click the Browse button. Locate the file and click ok to add it to Excel. To get your quotes, just enter a bunch (up to 25) of ticker symbols into cells and then go to Tools --> Get Stock Quotes from Yahoo!. Enter the data in the dialog box, click OK, and then answer yes when asked asked if you want to download the "quotes.csv" file (it is safe, just a text file from Yahoo!). |
| Duration and Convexity Calculator | This Excel worksheet demonstrates the calculation of duration and convexity. It also calculates the price change (for a selected yield change) predicted by duration and convexity (each and total). You can change all of the bond's features and see the results. |
| Asset Allocation Questionnaire | This Excel workbook presents you with a questionnaire (15 questions) designed to gauge your risk tolerance. After answering the questions, you can get a report with a suggested asset allocation. |
| Dow Jones Industrial Average Calculator | This workbook uses a Web Query to get the current prices of the 30 components of the DJIA and then calculates the average. |
| Coin Toss Stock Prices | This workbook uses a "coin toss" metaphor for creating randomly generated stock price charts. It is meant primarily to show that the same patterns (trends, head and shoulders, etc.) that can be seen in real charts can also be seen in simple randomly generated data. Does it prove that markets are efficient? Of course not, but it should make technical analysts think twice. Read my blog post, Coin Tosses and Stock Price Charts, for a full explanation. |
General Financial Spreadsheets |
|
| Amortization Schedule | This workbook will show an amortization schedule for any fixed-rate, fully amortizing loan (mortgage, car loan, etc.). I have posted a tutorial on www.TVMCalcs.com that shows how to create an amortization schedule, and how to make it look nice using conditional formatting.. |
| Calculating Accumulated Depreciation | This workbook shows how to calculate accumulated depreciation in a single cell using an array formula. It makes use of the DDB, Row, Indirect, and Text functions. There is an explanation in the spreadsheet, but you should read my blog post for a complete explanation. |
Monte Carlo Simulation |
|
| ExcelSim 2003 | This is an Excel add-in program I've written to perform Monte Carlo simulation. You will also need the documentation file and the examples file. A good list of available articles from a wide variety of fields is available from Decisioneering, Inc (makers of the Crystal Ball Excel add-in) here. |
| Uncertainty and Risk Analysis.pdf | A truly excellent introduction to modeling uncertainty with Monte Carlo methods by Chris Rodger and Jason Petch of PriceWaterhouseCoopers (April 1999). This document also provides an excellent discussion of many probability distributions and when they should be used. I can no longer find this on the Web, so I've posted it here. |
Excel Tips and Tricks |
|
| Array Formula Examples | This workbook shows several examples of the use of array formulas with complete examples of how they work. |
| Chart Tricks | This workbook shows a few charting tricks. Specifically, it shows how to use named ranges in a chart so that you can easily change the data series in a chart, also how to put multiple charts into one chart, and how to shade periods of recession when charting economic time series. |
| Roll the Dice! | This is a little file that does a graphical simulation of tossing a pair of dice. It isn't very useful, but its an interesting way to show how to use the IF function, conditional formatting, and concatenation in Excel. |
Excel Programming Tools |
|
| Add-in Express 2008 for Microsoft Office and .NET | An Excel toolkit for developing add-ins using .Net (Visual Studio, even the free Express version). This tool provides Excel developers with some very interesting features to create an advanced GUI for their Excel add-ins. Here are just a few examples:
|