Up and running again!

Alrighty, so I fixed a two out of three of my spreadsheets. These include:

I had to remove the “Previous Close” download feature which Yahoo! Finance used to facilitate. Unfortunately, this means users now need to enter this information in manually. I don’t believe this is a big issue though since this information is not crucial to the calculations carried out in the spreadsheets. Hope this isn’t too much of an inconvenience to anyone.

I’ve yet to find a fix for my Stock Screener spreadsheet unfortunately. It appears Yahoo! Finance was the only company who made such a good range of stock metrics available for download via API. Such a shame they no longer facilitate this feature.

They’ve done it again!

Yahoo! Finance have permanently discontinued their API feature for downloading stock quotes 🙁

When my spreadsheets try to access their data I get this message:

“It has come to our attention that this service is being used in violation of the Yahoo Terms of Service. As such, the service is being discontinued. For all future markets and equities data research, please refer to finance.yahoo.com.”

There doesn’t seem to be a work-around available just yet unfortunately. The effected spreadsheets are:

  1. Stock Valuation spreadsheet
  2. ASX Stock Screener
  3. Portfolio Risk spreadsheet

Apologies for any inconvenience caused. I would greatly appreciate any information people might have about possible work-arounds.

Stay tuned for the fixes…

Bug Fix 1

I’m pleased to report that all of my spreadsheets are up and running again. Big thanks to the guys over at XL Automation who have posted a work-around for Yahoo! Finance’s recent discontinuation of their API Feature. You can find a detailed explanation of the method they use as well as an example spreadsheet through their website here Yahoo Historical Price Extract. I modified the code slightly for compatibility reasons and implemented the fix into the following spreadsheets. All of which required the gathering of historical pricing data from Yahoo! Finance.

  1. Stock Valuation spreadsheet;
  2. Portfolio Risk spreadsheet;
  3. MV Portfolio Optimisation.

Thanks for your patience while I rectified this issue. Let you me of you experience any issues. As always, happy to assist wherever possible.

 

Cheers.

Bugs

It was recently brought to my attention that some of my spreadsheets were malfunctioning. It turns out that the Yahoo! Finance has discontinued their API feature and have no intentions of  reintroducing its functionality. As a result, some of my spreadsheets are not able to download the necessary data to carry out the intended calculations. My research indicates there is a way around this road block but it will require some time to implement. Therefore, until further notice, the following spreadsheets are not functioning at capacity:

  1. Stock Valuation spreadsheet;
  2. Portfolio Risk spreadsheet;
  3. MV Portfolio Optimisation spreadsheets.

Apologies for any inconvenience caused. I hope to have everything up and running again soon.

Cheers.

Yield to Maturity

Hey guys, this is the first Bond spreadsheet I’m uploading so I thought I’d start off with a relatively simple one – many more to come hopefully. This spreadsheet calculates the Yield to Maturity (YTM) of a non-callable, coupon paying bond.

Below is the link to download my Yield to Maturity spreadsheet:

Yield to Maturity

The spreadsheet adopts Newton’s Method to calculate the Bond’s yield. Newton’s Method is a numerical technique which converges on the yield by first calculating f(x); the difference between the Bond’s calculated price (using an initial guess for the yield) and the Bond’s purchase price (where x is the yield), the end goal being to equate f(x) to zero. And then refining the yield estimate via the following equations:

Where f’(xn) is the derivative of the difference between the Bond’s calculated price and the Bond’s purchase price. This process is then repeated until f(xn) is smaller than the specified tolerance. Convergence here is at least quadratic, meaning the number of correct digits roughly at least doubles each step which is why Newton’s is the favourable method for approximating the YTM.

The spreadsheet requires a number of inputs before the calculation can commence. These inputs are the following:

  • Coupon rate as an annual percentage;
  • Face value (or par value);
  • Years to maturity;
  • Number of coupon payments per year – this is typically 2 but the spreadsheet can accommodate any number of coupon payments;
  • Current market value of the bond; and
  • Desired tolerance which is essentially the users acceptable zero value – once f(x) falls below this value, the calculation will cease (for values below 1e-16, roundoff error is expected).

Once the user has input the required information and initiated the calculation, an approximation for the YTM as an annual percentage is output. The status bar will provide the user with progress information including the number of elapsed iterations and the convergence of the tolerance to that specified by the user.

The spreadsheet will also check the result by calculating the Bond price using the calculated yield and comparing that to the current market price – remember, these are meant to be the same.

Let me know how you go with this one. I’d be interested to hear how suitable the format is. Perhaps users might prefer a format where they can input a number of Bonds and calculate each of their yields in quick succession?

Mean-Variance Portfolio Optimisation

Hey guys,

Below is the link to download my MV Portfolio Optimisation spreadsheet.

MV Portfolio Optimisation

Basically, this spreadsheet determines how much of each stock an investor should hold for the next investment period (year/month/week/day) to minimise the variance (risk) of the portfolios returns for that period given a target return. The spreadsheet also plots the Efficient Frontier for the portfolio, and the required weight of each asset against portfolio target return.

Check out the instructional video below for a brief demonstration on how to use the spreadsheet.

Once the user has input the ticker symbol for each stock within their portfolio on the “Assets” sheet, they can initiate the download of all historical pricing data from Yahoo Finance. This data is then used to calculate the Variance-Covariance matrix based on a user-defined Holding Period (Day, Week, Month or Year). The inverse of the Variance-Covariance matrix can then be estimated, and the expected returns of each asset calculated. The expected return for each asset is the arithmetic mean of the historical returns for that asset based on the previously defined Holding Period, i.e. the Holding Period used to calculate the Variance-Covariance matrix. Now the user can optimise their portfolio, i.e. calculate the asset weights to minimise portfolio variance for a given target return, based on the theory below. The user will be prompted to input their desired target return as an annual figure when they click the “Optimise” button on the “Results” sheet. The target return will be appropriately scaled to coincide with the previously defined Holding Period. Finally, now the ‘a’ and ‘b’ vectors (refer below theory) have been calculated, the user can plot the Efficient Frontier of the portfolio, and each asset weight against target return.

The spreadsheet adopts Mean-Variance Portfolio theory, which is a mathematical technique that determines asset weights within a portfolio to minimise the variance (risk) of the portfolios returns given a target return for that portfolio. This spreadsheet assumes the portfolio is composed of only risky assets, specifically stocks listed on the Australian Stock Exchange (ASX). To minimise the variance for a given target return, MV Portfolio theory adopts the Method of Lagrange Multipliers, and after a bunch of tedious matrix algebra, the following relationship for portfolio weights is derived:

Where represents the Variance-Covariance matrix of the portfolio and µ represents a vector of each assets expected return. The ‘a’ and ‘b’ vectors shown in the above relationship are calculated and displayed in the “Results” sheet.

As can be seen from the above formula, the inverse of the Variance-Covariance matrix is required to compute the asset weights vector. Since the Variance-Covariance matrix is always square and generally non-singular, MS Excel’s “MInverse” function can normally calculate the inverse without issue. In the instance where there exists near-singular entries (values smaller than 1e-16) in the Variance-Covariance matrix, or if “MInverse” returns an error for some reason, the Moore-Penrose Pseudoinverse of the matrix will be estimated. Refer to my previous blog post, Moore-Penrose Pseudoinverse for more information on the algorithm used in this scenario.

So the reason why I went to all the trouble of building a VBA algorithm for calculating the Moore-Penrose Pseudoinverse was because the first Variance-Covariance matrix I tried to invert was singular (non-invertible). I stupidly assumed that this would be the case for all Variance-Covariance matrices… it isn’t. The Variance-Covariance matrix was only singular in that instance because I input the same stock twice (to represent the purchase of shares of the same company on two separate occasions) leading to multiple entries of the same value within the matrix, and the matrix to be non-invertible. In any case, the “Calculate Inverse” button on the “Results” sheet will produce at least an estimate of the inverse of the Variance-Covariance matrix. So the user can input the same ticker symbol any number of times and the portfolio can still be optimised but I would recommend the user only input each ticker symbol once, the calculation will be quicker this way and the result more simple.

I hope you find this spreadsheet useful. Let me know if you have any trouble or if you come up with any improvements I might be able to incorporate.

Enjoy!

Cheers,

Nick

Moore-Penrose Pseudoinverse Calculation

So this isn’t the type of spreadsheet I normally upload to nicksfinancetricks.com but it was something I spent quite a bit of time preparing and something that my research indicated was not easily found on the internet…

This spreadsheet calculates the Moore-Penrose Pseudoinverse (i.e. an estimate of the hypothetical inverse) of a matrix, a feature I thought was required for my soon to be released Mean-Variance Portfolio Optimisation spreadsheet. As it turned out, being able to calculate the Pseudoinverse of the Variance-Covariance matrix isn’t critical as this matrix is always square and seldom Singular. Nonetheless, this feature means an estimate for the inverse matrix can always be achieved, where the MS Excel built-in matrix inverse function can sometimes fail due to the presence of near singular values.

Below is the link to download my Moore-Penrose Pseudoinverse spreadsheet:

Moore-Penrose Pseudoinverse

I would appreciate any contributions to the VBA Subroutine if anyone is interested. The following link will take you to my Github repository:

Github – VBA Pseudoinverse

Ironically, what can be done in 1 line of code in MATLAB using the “pinv” built-in function took me almost 400 lines of code in VBA. But if you like working with spreadsheets like I do then you’ll appreciate the convenience of having the ability to calculate the pseudoinverse of a matrix and immediately use the result within your spreadsheet. Plus, not everybody has access to MATLAB.

Anyway, if you’re interested in the mechanics behind the pseudoinverse algorithm it’s probably worth reading on. There are three main components to the algorithm, these include QR Factorisation via Gram-Schmidt Orthogonalisation, Singular Value Decomposition (SVD), and finally the removal of any Singular Values so the pseudoinverse can be formulated.

The algorithm I used for the QR Factorisation function was obtained from Timothy Sauer – Numerical Analysis 2E. The following pseudo-code for the function was extracted from Sauer-Chapter 4.

For the SVD algorithm, I converted a MATLAB function prepared by a Paul Godfrey (23/10/2006) into a VBA function. Paul’s MATLAB function can be found on the Mathworks file exchange at the following link:

Mathworks – Simple SVD

Paul’s algorithm uses QR Factorisation on matrix-A (the matrix to be inverted) to gradually pull matrix-U out from the left and then uses QR Factorisation on matrix-A transposed to gradually pull matrix-V out from the right. The process makes matrix-A lower triangular and then upper triangular alternately. Eventually, matrix-A becomes both upper and lower triangular at the same time (i.e. diagonal) with the singular values on the diagonal. The algorithm outputs three matrices U, S and V where S is a diagonal matrix with any singular values and A = U*S*VT.

For the final step, I converted MATLAB’s own “pinv” function to a VBA function that uses the above SVD function in place of MATLAB’s “svd” built-in function. This step identifies any values in the diagonal S-matrix as singular if they are smaller than the user-defined tolerance. These singular values are removed from matrix-S so the inverse can be estimated without rounding errors forcing the result erroneously towards infinity. The function outputs matrix X of the same dimensions as AT so that A*X*A = A, X*A*X = X and A*X and X*A are Hermitian.

The other, more trivial, VBA functions required to produce the pseudoinverse are as follows:

  • EYE – returns identity matrix;
  • ZEROS – returns matrix composed entirely of zeros;
  • NORM – returns the length of a vector;
  • TRIU – returns the upper triangular part of a matrix;
  • DIAG – returns a column vector of the main diagonal elements of a matrix;
  • TransposeArray – returns the transpose of a matrix;
  • MultArrays – returns the matrix multiplication of two matrices of compatible size.

To assess the accuracy of the pseudoinverse, the result of multiplying matrix-A by matrix-X (pseudoinverse) is compared with the appropriately sized identity matrix. This is based on the notion that the inverse of a matrix multiplied by the matrix itself is equal to the identity, i.e. A*A-1 = EYE where EYE is the identity matrix. The difference between EYE and A*X is compared via the Frobenius Norm and printed to the “Start” sheet. The closer this number is to zero, the closer the pseudoinverse is to the hypothetical inverse.

The condition number of matrix-A is also estimated by dividing the largest absolute value found in the diagonal matrix-S (from SVD) by the smallest absolute value. This number can be used to estimate the expected number of correct digits produced by inverting matrix-A.

By way of comparison between MATLAB’s “pinv” function and the pseudoinverse algorithm I wrote in VBA, I’ve carried out a couple of tests. These tests were carried out on a 15-stock variance-covariance matrix. The results of which are summarised below:

Test Matrix-A size Tol. Cond. # Rank Singular values F-Norm (VBA) F-Norm (MATLAB) Max. Diff. Matrix Type
1 15 x 15 1e-16 8.27e+01 15 0 2.09e-14 1.56e-14 4.64e-11 Square
2 14 x 15 1e-16 8.24e+01 14 0 2.16e-14 1.96e-14 4.58e-11 Underdetermined
3 15 x 14 1e-16 8.24e+01 14 0 7.82e-02 1.00e+00 4.56e-11 Overdetermined
4 16 x 16 1e-16 6.52e+16 15 1 1.00e+00 1.00e+00 4.81e-11 Square
5 15 x 16 1e-16 4.90e+16 14 1 1.00e+00 1.00e+00 4.59e-11 Underdetermined
6 16 x 15 1e-16 6.61e+16 14 1 1.00e+00 1.41e+00 4.65e-11 Overdetermined

Where ‘Max. Diff.’ represents the maximum difference between MATLAB’s “pinv” function and my VBA algorithm.

So basically, for overdetermined matrices (matrices with more rows than columns, equivalent to systems with more equations than unknowns) and matrices with singular values, both VBA and MATLAB functions produce pseudoinverse estimations lacking in accuracy (since an accurate result is not achievable under these circumstances given double-precision data types and the tolerance specified). For underdetermined matrices (matrices with less rows than columns, or systems with fewer equations than unknowns) and square matrices without singular values, both MATLAB and VBA functions produce pseudoinverse estimations with up to approximately 14 decimal places of accuracy.

The MATLAB and VBA functions typically produce results of comparable accuracy according to the Frobenius Norms. The maximum differences observed between the results of the two functions are less meaningful in terms of accuracy but still serve as an indicator to correctness.

Portfolio Risk

Hey guys,

Below is the link to download my Portfolio Risk spreadsheet.

Portfolio Risk spreadsheet

In summary, this spreadsheet calculates the current risk, i.e. standard deviation, and expected return of a user-defined portfolio. The spreadsheet also calculates the constituent assets standard deviations and expected returns. Additionally, the spreadsheet can be used to assess the suitability of adding or removing assets from/to the portfolio. For example, the user can add additional assets, run the calculations again, save the results and compare with earlier portfolio configurations. If it is observed that the additional assets increase the portfolio risk, then perhaps the user will be less inclined to invest. The data used within the calculations is automatically downloaded from the Yahoo Finance website, the asset selection is restricted to ASX listed stocks whose historical pricing data can be found on this website.

Check out the instructional video below for a brief demonstration on how to use the spreadsheet.

In more detail, once the user has input the asset information of their portfolio, i.e. Ticker Symbol, Purchase Date (not mandatory), Number of Shares Purchased, Purchase Price (not mandatory), and Fees (not mandatory), information downloads and calculations can commence. The non-mandatory items aren’t required to calculate the portfolio risk but are useful to assess the progress of the user’s portfolio.

The following downloads and calculations are initiated by the user on the “portfolio” sheet:

  1. Previous Close: automatically downloads the previous closing prices for each asset in the portfolio from Yahoo Finance;
  2. Historical Price: automatically downloads the complete historical pricing data set for each asset from Yahoo Finance (this button will also add new data to each assets’ data sheet if clicked any number of days after initialising the portfolio);
  3. ASX List: automatically downloads the latest list of ASX listed companies from asx.com;
  4. Sigma-Day/Week/Month/Year: calculation which determines the portfolio variance-covariance matrix based on varying holding period durations, i.e. daily, weekly, monthly or yearly;
  5. Sigma-All: initiates calculations for all four of the above portfolio variance-covariance matrices;
  6. Sigma-Custom: initiates variance-covariance matrix calculation based on asset yearly returns, where the yearly returns are compound averages based on user defined holding period durations, i.e. daily, weekly or monthly.

Once the user has download all the relevant data and calculated the desired variance-covariance matrices, they can initiate the expected return and standard deviation calculations for each asset and the portfolio. Expected returns in this case are arithmetic mean returns. The following calculations are initiated by the user on the “results” sheet:

  1. Daily Statistics: calculates each asset’s as well as the portfolio’s expected returns and standard deviations based on daily holding period returns;
  2. Weekly Statistics: calculates each asset’s as well as the portfolio’s expected returns and standard deviations based on weekly holding period returns;
  3. Monthly Statistics: calculates each asset’s as well as the portfolio’s expected returns and standard deviations based on monthly holding period returns;
  4. Yearly Statistics: calculates each asset’s as well as the portfolio’s expected returns and standard deviations based on yearly holding period returns;
  5. All Statistics: initiates standard deviation and expected return calculations for all the above four holding period durations;
  6. Custom Statistics: calculates each asset’s as well as the portfolio’s expected yearly returns and yearly standard deviations based on compound average returns with duration corresponding to that defined by the user for Sigma-Custom;
  7. Save Portfolio: copies the current portfolio configuration as well as all the calculated statistics to the “saves” sheet, the data is catalogued so it can be filtered and compared with other portfolio configurations.

It should be noted here that the spreadsheet uses only historical pricing data available from Yahoo Finance, specifically the adjusted closing price. Dividend payments are not included in holding period return calculations.

The way I use the ‘Save Portfolio’ feature is by first inputting my portfolio in its current state and then calculating each variance-covariance matrix, expected return and standard deviation. Then I save the configuration with a suitable name, say “myPortfolio”, for later use. Next I add any additional (or remove unwanted) assets I’m interested in investing in and repeat all calculations. After saving the new portfolio configuration I can filter by “PORTFOLIO” under the “Company Name” heading on the “saves” sheet and compare the results of the two configurations. If the new configuration shows a reduction in risk (standard deviation) and/or an increase in expected return I would be more inclined to make the changes to my portfolio. Just remember to consider all fees which might accompany such an amendment to the users portfolio.

I hope you find this spreadsheet useful. Let me know if you have any trouble or if you come up with any improvements I might be able to incorporate.

Enjoy!

Cheers,

Nick

Example ASX stock screen and valuation

Hey guys,

Today we’re going to run through a couple of examples using my Stock Screener spreadsheet to hone in on some cheap stocks, and then my Stock Valuation spreadsheet to estimate the value of those stocks to us as investors.

At the risk of ruining the surprise, the stocks I value in this post are the following:

  • Metcash Limited (MTS);
  • St George Mining (SGQ); and
  • Crown Resort Limited (CWN).

Check out the demonstration video below to see how the three company’s performed using the Stock Screener and Stock Valuation spreadsheets.

Download the applicable spreadsheets at these links: ASX Stock Screener and Stock Valuation spreadsheet.

To hone in on cheap stocks I use the Price-EPS ratio and Price-Book Value ratio. For the former, I filter out all stocks with a ratio greater than 15. For the latter, I look for stocks with a ratio between 0.6 and 1.5. This screen yields about 150 cheap stocks, some of which worthy of further investigation. I personally like to go for large Market-Cap stocks so that is the next search criteria I use. From there, I wanted to analyse stocks not in the “Real Estate” GICS Industry Sector (personal preference really, large Market-Cap Real Estate stocks tend to dominate the low P/E universe of ASX stocks and I like to choose from the minority).

Once I’d narrowed my stock list down to MTS, SGQ and CWN, I used my Stock Valuation spreadsheet to estimate the value of these companies by calculating their systematic risk – beta, and their cost of equity via the Capital Asset Pricing Model, and finally the sum of their discounted, expected future cash flows via the Dividend Discount Model.

A note I made in the demonstration video which I’ll make again here is that my Stock Valuation spreadsheet will only tell you if the company is presently undervalued or not. There’s a bunch more research and analysis the user/investor should carry out before investing in an undervalued stock. These include the following:

  • Assessing the competency of the managers of the company;
  • Ensuring the company has long term prospects (e.g. a product which will always be in demand);
  • Ascertaining the stability and understandability of the company (look for company’s with ROE figures which consistently exceed 10% with little fluctuation).

If the above sounded familiar, you’re not crazy – I’m not trying to reinvent the wheel here. I believe these are the criteria Warren Buffett uses to select the stocks he invests in.

In addition to the above, you might also like to check the compatibility of your new stock picks with the rest of your portfolio. I’ll be soon to upload a couple of spreadsheets which will do just that. One will estimate the variance of your portfolio, and whether adding a stock adequately reduces the overall portfolio risk. The other is slightly different but more exciting in my opinion, after inputting the ticker symbols of your portfolio and clicking a few buttons, the spreadsheet will calculate and plot your portfolio’s efficient frontier. It will also calculate the optimal weights of your portfolios assets for a given target return by minimising portfolio variance.

As covered in the demonstration video, there are a couple of circumstances where my Stock Valuation spreadsheet is unsuitable for estimating the current value of a company. This was the case for St George Mining (SGQ). According to the Morningstar data available to free members, SGQ had consistently and significantly negative ROE values for the past three years. This in combination with no dividend payments and thus dividend payout ratios of zero over the same time-frame meant the company growth rate could not be calculated and the Dividend Discount Model could not be used.

During the analysis of Crown Resort Limited (CWN) a separate issue was encountered. Given CWN has only be listed on the ASX since December 2007, shortly before the GFC hit, and the sample statistics presented on the “CAPM” sheet are calculated based on common time horizons (i.e. only market data since December 2007 is included), the expected value for the market (ASX200 in this case) is negative for three out of four holding period durations. The negative expected value produces spurious cost-of-equity values when used in the Capital Asset Pricing Model, and thus the results presented on this sheet, in this instance should not be utilised. Fortunately, the results presented in the “CAPM2” sheet are sound. This is because the expected value used here is based on market returns over the life of the index (since the ASX200 began in 1992). This longer time horizon is less affected by the large, negative returns of the market in 2008 and 2009 following the GFC.

As mentioned above in the recommended additional analysis points, stability and understandability of a company is important. This is usually assessed by a fundamental analysis of the company’s financial statements. I presently have a spreadsheet in my portfolio which analyses this information but it is quite convoluted and it requires the user has access to Morningstar’s DatAnalysis service which few individual investors do. I will work on improving this spreadsheet and upload it when its ready.

As usual, let me know if you have any issues or recommendations. Happy to assist where possible.

Cheers,

Nick

ASX Stock Screener

*** Yahoo Finance discontinued their API featuring meaning this spreadsheet can no longer download stock data. Sorry guys ***

Hey guys,

Below is the link to download my ASX Stock Screener spreadsheet.

Download Spreadsheet here!

The spreadsheet allows the user to screen for ASX listed stocks based on company data obtained from Yahoo Finance using the websites API. The “tags” I used in my spreadsheet were gathered from a blog by a lad named Ido Green at the following website:

Yahoo Finance (hidden) API

More information can be found here if the user desires.

The following instructional video briefly explains how to use the Screener.

After downloading and opening the spreadsheet the user first updates the list of ASX companies using button 1 on the “Screener” sheet. This extracts a .csv file from www.asx.com [full link here] and populates the “Screener” sheet with a complete and current list of ASX listed companies along with their ticker symbols and GICS Industry Group.

Next the user has the option of downloading the pre-set array of information for each company or customising the information that will be downloaded from Yahoo Finance via the websites API. To download the pre-set information, the user simply clicks button 2 on the “Screener” sheet. Alternatively, the user can add to or delete items shown in the list on the “Tags” sheet. The full list of headings available for download via Yahoo Finance API is shown on the “Yahoo API” page. The user can simply copy the desire headings along with their tags and paste them at the bottom of the list on the “Tags” sheet. Clicking button 2 now will download the user defined headings.

The user may need to apply a filter to the top row once all downloads have completed otherwise the screener is good to go. The user is free to use all the convenient features of Excel as they wish. I have already applied some conditional formatting to the first two columns, Price-EPS ratio and Price-Book Value ratio, which help to filter out possibly overpriced stocks. The user should keep in mind that a number of columns have already been formatted based on the pre-set headings which means the user may need to re-format some columns if the list of tags is changed substantially.

I hope you find this spreadsheet useful. Let me know if you have any trouble or if you come up with any improvements I might be able to incorporate.

Happy screening!

Cheers,

Nick

%d bloggers like this: