Quant Modeling: Intrinio Financial Data Case Study

We like to highlight certain cases where Intrinio Financial Data makes a huge difference for our users.

This semester, students in the University of Tampa's Investments class used the Intrinio Excel add-in to help them understand quant modeling.

Quantitative Modeling:

A financial analysis technique that uses mathematical and statistical modeling, measurement and research to predict real world outcomes for financial instruments. Quantitative analysts assign numerical values to variables to replicate reality mathematically.

The hardest part about quant modeling is gathering the data necessary to build it. Without the use of innovative tools like the Intrinio Financial Data Feed, students (and professionals) can be left spending (wasting) hours entering the data before they can actually analyze it.

We estimate that Intrinio saved each student at University of Tampa an average of 10 hours of data entry during their studies of quant modeling.

The class began by quickly installing the Intrinio Excel add-in on each student's own personal computer (Mac OS X & Microsoft Windows). The entire install process took less than 5 minutes, and the students followed along with the directions and Youtube video on the Intrinio website.

Downloading the Intrinio Excel add-in

The professor had selected a group of metrics that he wanted the students to include in their quant model.

These metrics included:

  • ROE
  • Book to Price Ratio
  • Earnings to Price Ratio
  • Cash Flow to Price Ratio
  • Trading Volume to Market Cap
  • 1 Month Excess Return
  • 2 Month Excess Return
  • 6 Month Excess Return
  • 12 Month Excess Return

The students added these metrics across Row 1.

Next they hard coded two dates into two cells to reference throughout. Current date and the 'start-date' so that they can be referenced and dynamically updated.

  • =TEXT(Now(),”YYYY-MM-DD”)
  • =TEXT(Now()-365*2,”YYYY-MM-DD”)

Typing in "AAPL" into the Row 2 under "Ticker Colum" to start, the students set out filling in the formulas for each cell across.

ROE

=IntrinioDataPoint(“ticker”,”roe”)

The students typed this into the cell beneath ROE, referencing "AAPL" in A2.

Earnings to Price

=IntrinioDataPoint(“ticker”,”earningsyield”)

The students typed this into the cell beneath Earnings to Price, referencing "AAPL" in A2 (earnings yield is the Intrinio tag).

Book to Price

=1/IntrinioDataPoint(“ticker”,”pricetobook”)

We have 1 divided by the price to book ratio to take the inverse.

Quant Model With Intrinio Financial Data - Calculating Price to Book

Cash Flow to Price

=1/IntrinioDataPoint(“ticker”,”evtoocf”)

We have enterprise value to operating cash flow, but we don’t calculate out the price to cash flow ratio in our tag set. Technically cash flow is for the whole entity and not just the equity, and it would be inconsistent to do a ratio for the whole entity versus just the equity. We take the inverse of EV to operating cash flows.

Trading Volume to Market Cap

Trading Volume

=(IntrinioDataPoint(“ticker”,”volume”)*IntrinioDataPoint(“ticker”,”close_price”))

Market Cap

=IntrinioDataPoint(”ticker”,”marketcap”)

Trading Volume to Market Cap

=(IntrinioDataPoint(“ticker”,”volume”)*IntrinioDataPoint(“ticker”,”close_price”))

/ IntrinioDataPoint(“ticker”,”marketcap”)

Volume is the number of shares traded, but we want value of number of shares traded, so we must multiply by the value of the shares traded.

One Month Excess Return

Stock price for most recent period

=IntrinioHistoricalPrices(“ticker”,”adj_close”,0,”start date”,”current date”)

Stock Price Over 21 Day Trading Period

(most recent divided by previous period minus 1)

=(IntrinioHistoricalPrices(“ticker”,”adj_close”,0,”start date”,”current date”)

/

IntrinioHistoricalPrices(“ticker”,”adj_close”,20,”start date”,”current date”) )-1

Excess Return Over The S&P500 Over The 21 Day Trading Period

(copy whole formula and subtract it, same formula substituting in the S&P ($SPX) for the ticker)

 =(IntrinioHistoricalPrices(“ticker”,”adj_close”,0,”2013-10-01”,”current date”)

/

IntrinioHistoricalPrices(“ticker”,”adj_close”,20,”2013-10-01”,”current date”) -1)

-

 (IntrinioHistoricalPrices(“$SPX”,”close”,0,”2013-10-01”,”current date”)

/

IntrinioHistoricalPrices(“$SPX”,”close”,20,”2013-10-01”,”current date”) -1)

Quant Modeling With Intrinio Financial Data - Calculating One Month Excess Return

Two Months Excess Return

Copy WHOLE formula, paste it and change 20 to (20*2)

=(IntrinioHistoricalPrices(“ticker”,”adj_close”,0,”2013-10-01”,”current date”)

/

IntrinioHistoricalPrices(“ticker”,”adj_close”,20*2,”2013-10-01”,”current date”) -1)

-

 (IntrinioHistoricalPrices(“$SPX”,”close”,0,”2013-10-01”,”current date”)

/

IntrinioHistoricalPrices(“SPX”,”close”,20*2,”2013-10-01”,”current date”) -1)

Six Months Excess Return

Copy WHOLE formula, paste it and change 20 to (20*6)

=(IntrinioHistoricalPrices(“ticker”,”adj_close”,0,”2013-10-01”,”current date”)

/

IntrinioHistoricalPrices(“ticker”,”adj_close”,20*6,”2013-10-01”,”current date”) -1)

-

 (IntrinioHistoricalPrices(“$SPX”,”close”,0,”2013-10-01”,”current date”)

/

IntrinioHistoricalPrices(“SPX”,”close”,20*6,”2013-10-01”,”current date”) -1)

Twelve Months Excess Return

Copy WHOLE formula, paste it and change 20 to (20*12)

=(IntrinioHistoricalPrices(“ticker”,”adj_close”,0,”2013-10-01”,”current date”)

/

IntrinioHistoricalPrices(“ticker”,”adj_close”,20*12,”2013-10-01”,”current date”) -1)

-

 (IntrinioHistoricalPrices(“$SPX”,”close”,0,”2013-10-01”,”current date”)

/

IntrinioHistoricalPrices(“SPX”,”close”,20*12,”2013-10-01”,”current date”) -1)

Finishing Up The Quant Model

One the students had the formulas filled in for one stock (AAPL, in our example) it took a matter of seconds to fill in the rest of the model. The students simply added a list of tickers below AAPL, highlighted the row of formulas, and dragged it down. When you click Recalculate, the data will automatically populate. It's as simple as that!

Quant Model With Intrinio Financial Data

After the students successfully pulled in all of the data and metrics they needed (in about 10 minutes instead of hours) - they were ready to start digging into building the model.

 

If you are a student or professor interested in Intrinio Financial Data or have questions about this Quant Model exercise, please click the green chat button in the right hand corner of this website. We promise to get back to you within the hour.

 

WELCOME TO #DATAFREED NOT #DATAFEED

Get started for free today by visiting www.intrinio.com. Register with your email, visit our pricing page, and check out our documentation or Youtube videos for help getting started. We're always available via our Support Page if you need help. 

Follow us on Twitter and LinkedIn and Like us on Facebook to stay up to date.