Re: WLSRegression using Excel VBA
You could actually simplify this by removing (try commenting first, of course) every line after your declaration of b. Then, your last line will just be: WLSregression = b Or you can just skip the need for b completely and just have: WLSregression = Application.WorksheetFunction.MMult(XwXin... You could actually simplify this by removing (try commenting first, of course) every line after your declaration of b. Then, your last line will just be:

WLSregression = b

Or you can just skip the need for b completely and just have:

WLSregression = Application.WorksheetFunction.MMult(XwXinv, Xwy)
(By the way, you could just use Application.MMult here.)

If, for some reason, you needed the k and output and all that, then you have to fix the indexing. Basically, VBA arrays start with index 0 by default. So the first element in output would be output(0). However, note that you are initializing bcnt = 1 in your loop. Thus, the first iteration will be setting output(1) to b(1, 1). The second iteration will work on output(2). But what about index(0)?

In essence, you skipped index(0) and that's why an empty 0 is sitting there in the first spot. You could fix this by simply changing that line to:

output(bcnt - 1) = b(bcnt, 1)

Now it works as expected! Read More

Re: WLSRegression using Excel VBA
Hi Brendan, After looking through your code a bit more, it looks like it actually does work as intended, as long as you first select three cells going across, paste in the formula, and hit Ctrl + Shift + Enter to make it an array formula. The formula I tested with is: =TRANSPOSE(WLSregression... Hi Brendan,

After looking through your code a bit more, it looks like it actually does work as intended, as long as you first select three cells going across, paste in the formula, and hit Ctrl + Shift + Enter to make it an array formula.

The formula I tested with is:
=TRANSPOSE(WLSregression(B2:B17,C2:E17,A2:A17))

(I entered the data you supplied in A2:E17)

Let me know if trying those steps works for you.
Read More

Re: WLSRegression using Excel VBA
Let's start with that random data you have there. We could do a few useful things, like generate a covariance matrix, etc.

What's the desired behavior of the WLSregression function? Should it calculate and then return the three calculated coefficients, or something else?

Re: VLOOK and MATCH
Hi Peter, In step 3, the MATCH function will always only point you to one value/position. In other words, if on those three trading dates, the stock all closed at exactly $50.00, then your result would simply show the first date in the search. So in this case, the latest dates are at the top, des... Hi Peter,

In step 3, the MATCH function will always only point you to one value/position. In other words, if on those three trading dates, the stock all closed at exactly $50.00, then your result would simply show the first date in the search. So in this case, the latest dates are at the top, descending into older dates at the bottom. Therefore, Excel will start at the top, and go down the list, looking for the first instance of $50.00 that it finds.

(Note that this is assuming a match_type parameter of 0; if you were to specify it to -1, then it would find the smallest value greater than or equal to $50.00, which is not really useful in this example.)

If you wanted to get *all* the different dates for a particular closing price (e.g. given the price $50.00, you wish to identify all the dates on which it closed at that price), you would have to take a different approach. For example, you could use an array formula that could get quite convoluted. You might also use Excel's built-in filter tools. Read More

Re: Valuation Summary - Private Company Differences
Hello, For valuation of private companies, there are many techniques that unfortunately is too extensive to go into on this forum. The challenge is that there are too many nuances given the relatively lack of information available on private companies. Even if you have access to management and inte... Hello,
For valuation of private companies, there are many techniques that unfortunately is too extensive to go into on this forum. The challenge is that there are too many nuances given the relatively lack of information available on private companies. Even if you have access to management and internal financials (which is the only way to approach this), there are many market related information that is not available given the simple fact that they are not publicly traded or listed. Good news is, we have an entire separate course related to the valuation of private companies. The better news is that it is available online. If you are logged into our learning management system, please go to the top right and click on ORDER MORE, Advanced and Industry Modules and look for Package: Private Company Valuation. Thank you. Read More