Financial Modeling with Free Intrinio Data in Excel and API

If you think financial modeling is buying expensive clothes to impress your friends, this article isn't for you. If you think financial modeling requires data entry to constantly update your calculations, you are in for a treat. This article shows how to calculate a discounted cash flow (DCF), weighted average cost of capital, or quantitative model in Excel or via API automatically, without data entry.

Where Does the Data for the Financial Model Come From?

Traditional finance courses teach students to manually enter data into an Excel spreadsheet to perform a DCF analysis and come up with a valuation for a security. Here is a good example of the traditional approach on youtube. As you can see, several hundred thousand people have seen this video and learned how to build a cash flow statement manually in Excel.

This is a great video, and the methods are excellent. The problem is no real person has time to model data this way. It requires too much manual data entry. Here is an example of Intrinio's method:

As you can see, Intrinio pulls in the statement of cash flows, as well as the balance sheet and income statement, automatically. The data flows from Intrinio's database, which is updated continuously with the latest SEC filings and stock prices, directly to your spreadsheet. This makes the hard part of financial modeling (data entry) easy. Every time you refresh your model, you have the latest data.

Its hard to over emphasize how radical this concept is. Right now, thousands of finance students are getting ready to spend hours doing data entry. Millions of investors and professional financial analysts are paying thousands of dollars a month to traditional data providers for this service. Intrinio is providing it for free.

Financial Modeling: A DCF and WACC In Excel

Because Intrinio provides the data and takes care of the data entry, its easy to run a DCF or WACC for any publicly traded US company. Intrinio provides a template for this in the free Excel add-in download. You simply put in a ticker and hit update:

Financial Modeling Made Easy

Financial Modeling Made Easy

Notice the Income Statement and Balance Sheet tabs along the bottom:

Income Statement For a Financial Model

Pulling the Income Statement Automatically

Balance Sheet for Financial Model In Excel

Pulling the Balance Sheet Automatically

If you click into the WACC model, you can see the real magic:

Weighted Average Cost of Capital Financial Modeling

WACC In Excel In Seconds

And finally, the DCF, completed in seconds:

Discounted Cash Flow In Excel for Financial Modeling

DCF In Excel In Seconds

It's important to remember that these financial models are built with default assumptions. The point is that the hard work is done for you, and the valuable work (adjusting your assumptions to tune the model) can now begin.

Financial Modeling Via The API

Everything you've seen in this article can be done via application programming interface (API) in the programming language of your choice. In fact, the Excel add-in is really just a wrapper of the API. Excel is making API calls in the background to pull the latest data into each cell.

This article shows how to get started with the Intrinio API. There are SDKs and sample code in languages like Python, R, Ruby, C#, Node JS, .NET, PHP, Swift, and Visual basic. Authenticating is easy- you can get API keys for free immediately if you create an account at Intrinio.com/login.

This article shows how the API can be used in R to perform financial modeling via logistic regression, machine learning tree models, and linear regression. The article shows the exact code, but these two API calls show the basic idea:

https://api.intrinio.com/prices?ticker=AAPL

https://api.intrinio.com/historical_data?ticker=AAPL&item=ebitda&start_date=2009-01-01&end_date=2017-01-01

The first API call pulls in the stock price history for Apple from yesterday's close price back to the 1970s. That's 40+ years of daily prices. That is your dependent variable, the data you want to predict with your explanatory variables.

The second API call pulls in Apple's EBITDA over an 8 year period. This is an example of an explanatory variable, something an analyst could use to predict the stock price.

Using these two very simple API calls a developer can pull thousands of financial metrics and build very basic, or very advanced, quantitative financial models to predict stock prices.

What's the catch?

Intrinio charges for some of its data feeds. No surprise there. It is surprising that Intrinio's data is affordable on a student budget and plans that include redistribution cost hundreds, not thousands, of dollars. Comparable plans for professional analysts from other data providers can easily cost 10 times Intrinio's prices.

It should also be surprising that Intrinio provides free plans for the US & Global Economic data feed as well as the US Public Company Financials data feed. The later includes real time stock prices, an unprecedented value, as well as full access to the entire cash flow, income statement and balance sheet for any public company.

This free access means there is no excuse for manually entering data when financial modeling. Intrinio was built to make financial data easy to access and affordable so our users can save money and make time. We hope you will save money using the tools showcased in this article and make time by skipping the manual data entry involved in traditional financial modeling. This time and money should lead to better financial decisions.