Financial Modeling with Intrinio Data in Excel and API

Andrew Carpenter
January 17, 2017

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.

It's hard to overemphasize 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 the Excel add-in for free, and the data needed to power the add-in at a competitive price.

Financial Modeling: A DCF and WACC In Excel

Because Intrinio provides the data and takes care of the data entry, it's 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.

Financial Modeling Via The API

Everything 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. If you're new to Intrinio, request a consultation with our team to create a custom data solution.

The API can be used in R to perform financial modeling via logistic regression, machine learning tree models, and linear regression. These two API calls show the basic idea:

NOTE: The examples below are for v1 of the Intrinio API. If you’re using API v2, follow the v2 documentation instead.

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 its data feeds. No surprise there. It is surprising that Intrinio's data is competitively priced for businesses and include flexible redistribution rights. Comparable plans for professional analysts from other data providers can easily cost 10 times Intrinio's prices.

This 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.

Ready to get started? Request a free, one-on-one consultation with one of our data experts.

Sorry, we no longer support Internet Explorer as a web browser.

Please download one of these alternatives and return for the full Intrinio Experience.

Google Chrome web browser icon
Mozilla Firefox web browser icon
Safari web browser icon
Microsoft Edge web browser icon
Microsoft Edge