Free Valuation Resources
Build Your Own Industry Risk Premium â€“ for Free
There are a number of resources in the internet that business valuation analysts can use and incorporate into their practices. Best of all, these resources are free. With a little knowledge of excel, basic understanding of API and how it works, and knowing where to look, practitioners can readily use this information in a valuation engagement. What follows is a guide for practitioners interested in programming and building their own Industry Risk Premium (IRP).
By the end of this brief article you will take the first steps to being able to calculate your own industry risk premium free of charge just by pressing a button.Â I realize thatâ€™s a very big claim, so to verify it I tried it out on a valuation friend that I know has no programming skills whatsoever, despite being an awesome valuator.Â In fact, since I wonâ€™t be mentioning this personâ€™s name here I think it is fair to say that even his/her Excel skills do not rank in the top quartile amongst finance professionals.
Iâ€™ve paraphrased this personâ€™s skepticism in a Q/A format below so that you can prove to yourself, the same way they did, that what Iâ€™m proposing is possible for any valuator who wants to take their practice to the next level.
Keep in mind, this only the first step.Â To automatically calculate your own IRP, ERP, Beta, or just about any other valuation input you are currently using will require eight more steps which we will cover in subsequent articles (here in QuickRead).Â But hopefully this initial exercise should convince you that your existing skills combined with modern web-based programming interfaces make it completely possible to do so.
Is this a pitch for valuation software?
Yes, but itâ€™s not a pitch to sell you valuation software or any other kind of software.Â Itâ€™s a pitch for you to learn how to make your own valuation software and thereby gain greater knowledge and control over the assumptions that go into every valuation you perform.
Do I need formal programming skills to successfully make software that can help perform valuation analysis?
No, you do not need formal programming skills to be successful at this.Â All you need are the same CPA skills that have served you and your clients well thus far.Â If you are a CPA, those skills are simply Cut, Paste, and Analyze (â€śCPAâ€ť).Â If you are a non-CPA CVA they are the same skills: Cut, [ctrl+V] and Analyze (â€śCVAâ€ť).
Youâ€™ll also need some free (Open Source) software applications discussed later in the series, a copy of Excel, and access to free Application Programming Interfaces (APIs) available online. Â Â But for today, all you need is a browser connected to the Internet and a copy of Excel on the same machine.
That seems too good to be true Lorenzo.Â I honestly donâ€™t see how thatâ€™s possible.
Fair enough.Â So before describing what an API is Iâ€™m going to prove that you can use one to get useful valuation inputs simply by cutting and pasting, as I suggested previously.Â After all, seeing is believing.
Sample Hypothetical Use Case.
You have a friend that owns a small tech company and theyâ€™ve asked you to review a valuation that was done as part of their estate planning efforts.Â The valuator used a number of publicly traded companies (guideline public companies) to arrive at multiples that were applied to your friendâ€™s company as the primary indication of total equity value.Â Youâ€™re skeptical and ask what the ticker symbols used by the valuator were and are told, they are as follows:
MSFT, AAPL, GOOGL, AMZN, FB, SPLK, FUEL, YHOO
Immediately recognizing some of the tickers as the largest tech companies, your skepticism turns to pessimism.
You tell your friend that many of those companies are much larger than their company and therefore probably not good analogs for the multiples a hypothetical buyer or seller would consider for a firm doing only $1.2 million in sales (your friendâ€™s annual revenue).
Youâ€™d like to go further in conveying the size difference without actually having to send a bill to your friend, so you simply paste a link youâ€™ve programmed to speak to the Yahoo! Finance API into your browser and in a few seconds a spreadsheet (CSV file) pops up with the details.Â Go head, give it a try.Â Paste the link below into your browser or simply click on it:
When you click on the link, or place it in your browser youâ€™ll get a message that data is being downloaded from Yahoo! and shortly therafter youâ€™ll be given the opportunity to open the file.Â You may be asked by Windows for credentials, if so just click the â€śCancelâ€ť button when prompted for a username and password.
After you click open a spreadsheet (.csv file) similar to the following table will appear.
|408.64M||Rocket Fuel Inc.||0.93||FUEL||N/A||8.76|
*Note:Â Column labels and related bold type formatting entered manually for illustrative purposes
Congratulations!Â If youâ€™ve never done this before, youâ€™ve just made your first API call. Â Please tweet #1stAPICall@NACVA to inspire others to take the first step also.
In layman terms this is what the link (URL) we pasted into the browser window is doing for us:
The question mark â€ś?â€ť in the following URL indicates that we are about to request some data (asking a question or â€śQueryâ€ť) about some data Yahoo! has that we want returned.Â It separates the path to the resource we need from the fields we would like to get returned.
Query String Parameters
The question mark is followed by what are referred to as â€śquery string parameters.â€ťÂ The same way an unlevered beta sounds more complicated than it really is.
For now, just consider a parameter as having two parts:Â a name and a value (or a series of values).Â So if I told you one parameter name was â€śTicker Symbolâ€ť you would automatically think of some possible values, such as those discussed previously (GOOGL for example).
Generally, the parameter name and value(s) are separated by an equal sign â€ś=â€ť, which for finance professionals typically seems very intuitive.
In the Yahoo! Finance API, â€śsâ€ť is a parameter name and it stands for â€śSymbolâ€ť, as in ticker symbol.Â So to let the API know you want data returned related to Microsoft, you would set the symbol parameter value to Microsoft as follows:
Some parameters can accept multiple values and the symbol parameter (s) in the Yahoo! API is a great example of this.Â Each of these values has to be separated by some kind of character the API recognizes as serving that purpose.Â In the case of this Yahoo! API, you can use either the plus symbol â€ś+â€ť or a comma â€ś,â€ť to separate multiple symbol values in your request.Â In the previous example, we used commas, but using plus signs to separate the tickers will return the exact same CSV file.
Separating Parameter Names
By convention, name value pairs (such as parameter names and values) are separated by an ampersand.Â So, if you look at the URL above, youâ€™ll notice that after weâ€™ve set the symbol â€śsâ€ť values to each ticker, an ampersand â€ś&â€ť appears followed immediately by the â€śfâ€ť parameter, which indicates a tag or property in the Yahoo! Finance API.
Youâ€™ve seen this, perhaps without noticing it, earlier today if you searched for something on Google for instance.
If you click on this link:Â https://www.google.com/search?q=NACVA+quickreadÂ youâ€™ll get search results for â€śNACVA QuickReadâ€ť from the Google Search API.Â The query string (keyword value) is separated here by the plus â€ś+â€ť symbol as we did with tickers symbols in the Yahoo! Finance API example.
Youâ€™ll notice that Google returns every possible result to this query resulting in multiple pages.Â We can add a tag named â€śnumâ€ť and limit the number of results we get from the Google API.Â To do this, we have to let the API know where the keyword values end and the property/tag begins. Â As before, we simply use the ampersand (â€ś&â€ť) to separate the search term parameter from the number of results tag and then set the results tag (num) equal to 1 if we only want one result shown on the page returned.
Now change the num tag value from â€ś1â€ť to â€ś2â€ť â€“ just copy and overstrike the â€ś1â€ť with a â€ś2â€ť and then paste into your browser.
Congratulations, you just programmed your second API call!
That was pretty easy right? Now, continuing with the Yahoo! Finance API, since itâ€™s the tool you will use to learn how to build your own industry risk premium for free simply by clicking a button youâ€™ve programmed using the simple techniques in this series.
You can set the tags/properties you want to retrieve for each of the symbols using the â€śfâ€ť parameter name (think â€śformatâ€ť if that helps).Â There are codes for each tag you want to retrieve.Â In our example we used the following tags:
S6 (which returns the sales/revenue associated with each ticker)
n (easy to remember â€“ returns the name associated with each ticker)
r (returns the PE ratio â€“ â€śrâ€ť as in â€śratioâ€ť since the PE ratio is the most popular public company ratio)
p5 (returns the Price/Sales ratio â€“ â€śpâ€ť as in price and the 5 kind of looks like an â€śsâ€ť as in sales)
j4 (returns EBITDA â€“ sorry, no memory peg but thereâ€™s no need to remember long term anyway)
In this particular API, the â€śsâ€ť stands for symbol.Â We already set the values for the symbol parameter previously, but we can have each symbol displayed separately in the order of our choosing in the output (.csv file) just by including the tag value of â€śsâ€ť again for one of the properties (â€śfâ€ť).
So hopefully now you believe me, that you can use your existing CVA or CPA skills (Copy, Paste, and Analyze), as I have done, to build software that will solve valuation questions and effectively give you greater capacity and better quality analysis than might otherwise be available.Â In the next part of this series, we will actually start placing some code into Excel so that instead of you speaking to the Yahoo! Finance API manually by pasting a URL into a browser, your code will dynamically make the request on your behalf.
Hopefully you now know, experientially, what a Web API is.Â Some additional background on APIs in general, and my efforts to introduce them to valuators in NACVA over the years, follows hereafter.
Whatâ€™s an API?
The simple answer is an API is an application programming interface.Â But that doesnâ€™t really do it justice, as even that acronym can mean different things based on the context.Â So Iâ€™ll begin by giving a little background into how an API can allow you to automate the process of gathering information (numbers, images, text, videos, you name it), making it easy for either you or an application youâ€™ve programmed to run further analysis on the data you receive back in response to your request.
Back in 2009 at NACVAâ€™s annual meeting, I demonstrated using an API as part of a valuation engagement.Â At the time few valuation analysts knew what an API was or how it could help them so only a couple of attendees came up to me afterwards to ask how the data was being brought into the valuation file automatically.
I entered a company name into a piece of software on my machine and a Risk Free Rate was pulled in from the Internet, Images and Bios for the Subject Companyâ€™s management team and directors were downloaded and organized, an Option Pricing Model was generated and an indicated total equity value for the company was created automatically. Â With one more click all of this data was exported nicely into Excel free of charge.
Now, each of those items could have been performed manually as follows:
- By clicking a link to the Federal Reserve website
- Manually navigating to the constant rate maturities
- Copying and pasting the appropriate rate that matched the term for the subject company into Excel
- Pasting a volatility input that matches the cost of equity (risk) of the subject company into a Black-Scholes model in Excel
- By clicking on a link to Crunchbaseâ€™s website
- Manually navigating to the subject company name
- Copying and pasting the amounts of private equity capital raised by each company and then pasting that amount, along with the issue dates, into Excel
- Manually navigating to the team profiles, clicking on each management team members name, and then copying and pasting their pictures and profiles into Excel
An API allowed us to do each of the 50 items (including subtasks) itemized above at the Boston conference just by clicking a button.Â That was nearly six years ago.Â Today, APIs are ubiquitous and incredibly easy to use.Â Spend a handful of hours learning how they work and both your billing capacity and your billing rate should increase dramatically.
Lorenzo Carver, MS, MBA, CVA, CPA is Founder and CEO of Liquid Scenarios, inventor of the Carver Import Algorithm and Search2Model, a patented valuation technology, and author of Venture Capital Valuation published by John Wiley & Sons. He is the author and sole developer of BallPark Business Valuation, the #1 selling, award winning small business valuation solution, and has authored over 120 written reports and more than 30,000 interactive models used by investors to value primary and secondary purchases of venture backed company securities. Carverâ€™s Liquid Scenarios technology has won numerous third-party certifications and awards. Mr. Carver can be reached at (303) 448-8800 or by e-mail at email@example.com.