Free Piotroski F Score Spreadsheet

Piotroski Score Spreadsheet

Update: Manual input is required to calculate the final scores. The spreadsheet no longer uses the excel plugin to grab data from the internet.

What is the Piotroski F Score?

Piotroski Score Spreadsheet

Keeping Score – Piotroski Score Spreadsheet | Flickr: shorts and long

The Piotroski F Score is a combination of 9 accounting checks to come up with a final score for the quality of the business. The Piotroski Score is certainly a number you want to consider when searching for investment opportunities.

Here is another description of the Piotroski Score.

A discrete score between 0-9 which reflects nine criteria used to determine the strength of a firm’s financial position. The Piotroski score is used to determine the best value stocks, nine being the best. The score was named after Chicago Accounting Professor, Joseph Piotroski who devised the scale according to specific criteria found in the financial statements. For every criteria (below) that is met the company is given one point, if it is not met, then no points are awarded. The points are then added up to determine the best value stocks. (Investopedia)

Profitability

The first four criteria of the Piotroski Score count towards the profitability.

1. Positive net income compared to last year (1 point)
2. Positive operating cash flow in the current year (1 point)
3. Higher return on assets (ROA) in the current period compared to the ROA in the previous year (1 point)
4. Cash flow from operations greater than Net Income (1 point)

Leverage, Liquidity and Source of Funds

Points 5-7 of the Piotroski Score, looks at the health of the balance sheet in teams of debt and the number of shares outstanding.

5. Lower ratio of long term debt to in the current period compared value in the previous year (1 point)
6. Higher current ratio this year compared to the previous year (1 point)
7. No new shares were issued in the last year (1 point)

Operating Efficiency

The last two factors of the Piotroski Score looks the operating efficiency.

8. A higher gross margin compared to the previous year (1 point)
9. A higher asset turnover ratio compared to the previous year (1 point)

How to Interpret the Piotroski Score

Enjoy what you're reading? Get Access to More
Get more tips and investing strategies in your inbox. I'll send you 9 FREE Investing Spreadsheets as a welcome bonus.

Obviously, the higher the score, the better. A company that achieves a score of 9 is fundamentally very strong. Any company that has a score 8 and above is considered excellent.

Introducing the Free Piotroski Score Spreadsheet for Excel

I’ve created a free spreadsheet for you to calculate the Piotroski score for any company. This is a very basic version that calculates the last 3 years of Piotroski scores, along with how each score was totaled.

The limitation is that financial companies such as banks, REIT’s and insurance companies cannot be calculated with the Piotroski Score spreadsheet as their GAAP financial statement format is different.

Screenshot of what the Piotroski spreadsheet looks like.

Simply enter the ticker into the yellow box and press calculate.

Piotroski Spreadsheet Requirements

  • Windows ONLY. Not mac compatible.
  • Requires Microsoft Excel 2003 and above

If you have never used this free Piotroski spreadsheet before, you will need to install a free Microsoft excel add-in called “SMF”, short for Stock Market Function, created by Randy from the Yahoo SMF group.

The premium version which includes 10 years of Piotroski Scores as well as many more features, do not require this add-in. It works without any additional add-in.

Installation Guide. Please Follow the Step by Step Instructions.

Please read the installation guide and FAQ.

If you still have issues, place all questions in the comment sections below. That way other visitors will be able to see solutions to problems right away.

How to Download the Free Piotroski Spreadsheet

To download the spreadsheet, you must go to the main page of Old School Value or look at the bottom of any page and you can sign up with your email to receive the list of free spreadsheets.

Premium Spreadsheets

Feel free to check out the free version and then when ready, go to the stock valuation software page and review what you will get with the premium.

The premium version includes several valuation models as well as fundamental analysis data, historical data, charts and competitor comparison features. Just by entering one ticker, you can immediately get all that information on your favorite stock which will save you hours in your analysis.

Go now and see for yourself why people rave about the spreadsheets.

About Jae Jun


Jae Jun is the founder of Old School Value. He is on a mission to provide practical and actionable value investing tools, tutorials and educational material to help empower the individual investor. Keep in touch with Jae via any of the methods linked below.

Get Tips and Strategies to Achieve Higher Returns
Bonus: Get 9 FREE Investing Spreadsheet Calculators
  • Graham Jervis

    Hi Jae,

    for some strange reason it is not working for me. the Altman Z works, and the OSV spreadsheets work.

  • Sp

    Ur work as always is great. Thx. Ur Altman z screener is wonderful. Would you happen to know of a good free altmanz screener? I have simple premise and simply want to buy puts on low z score companies right now while looking for quality

  • http://www.oldschoolvalue.com Jae Jun

    @ Graham,
    Still not working? There shouldn’t be anything different with this one.

    @ Sp,
    I’m using a custom screener but aside from that I believe you will have to use a premium screener such as AAII.

  • http://finance.groups.yahoo.com/group/smf_addin/ Randy Harmelink

    @ Graham
    Since it retrieves data from Morningstar, typically you need to create an IE cookie for the site before the add-in can access data from their site. That’s because they do some special redirection, sometimes for advertising purposes, on your first visit to the site each day. I usually just visit the site with the EXCEL Web Query process to create the cookie (keyboard shortcut alt-d-d-w). But it is one reason I tend to avoid using Morningstar as a data source.

    @ SP
    AdvFN has the Altman Z value in their screener product, but it requires (free) registration on their site to use. You might be able to do it on StockScreen123.com as well — I have set up a Piotroski screen there, and they do have a predefined one for Piotroski.

    @ Jae Jun
    You can also use element numbers 15001-15010 for current Piotroski items (using AdvFN data) and number 15011 for the Altman Z value.

    You could also retrieve it directly from AdvFN:

    =RCHGetTableCell(“http://www.advfn.com/p.php?pid=financials&symbol=MMM”,1,”>Altman”)
    .-= Randy Harmelink´s last blog ..Re: Help with RCHGetTableCell-Template-BarChart-Technical-Signals =-.

  • Sp

    thk you. I will check it out. Also your spreadsheets are extremely useful, thank you for those. I’m not sure but it seems like discount rate cannot be adjusted between 9 and 15 and that would be very beneficial (or at least) having a 12% discount rate option would be nice.

  • http://None Vilas

    Jae,

    First, thanks for the free spreadsheet. Is it possible for you to allow changing the link to SMF plugin location? The update links is disabled and I have installed the plugin at a different location for another application’s use. I try to have two copies of the same stuff in different locations.

    - Vilas

  • http://www.oldschoolvalue.com Jae Jun

    @ Sp,
    Are you referring to the free or premium valuation spreadsheets?

    @ Vilas,
    Can you send me a reminder email or through the contact form?

  • Tim2.0

    Very cool. Thanks for the work Jae.

    Just wanted to point out one thing with regard to Piotroski scores. Piotroski’s original paper was written with the 9 criteria being applied to those companies with P/B ratios in the lowest 20th percentile of all stocks. The idea was that stocks with a low P/B tend to outperform in aggregate, but there is a wide variability in whether any single low P/B company will outperform. Piotroski designed the 9 criteria with the intent of trying to filtering out the “good” low P/B companies from the “bad”.

    Thus, there are effectively 10 Piotroski criteria: the 9 mentioned plus the requirement that the stock be in the lowest quartile with respect to P/B.

    Many times the low P/B criteria is inadvertently bypassed by people in discussing these F-scores.

    Thanks again for a great resource.

  • Tim2.0

    @Tim2.0: “lowest 20%”, that is.

  • Vilas

    Jae,
    I was referring to the free Piotroski score spreadsheet. And I meant to say ‘I try NOT to have two copies of the same stuff in different locations.’ in the earlier note.
    Vilas

  • Vilas

    I figured it out. Thanks. This is good work. I discovered your website a couple months ago and am impressed by the content. Keep it up!

  • http://www.oldschoolvalue.com Jae Jun

    @ Time2.0
    Interesting point. What P/B ratio do you think I should be searching for? Just less than 1?

  • Tim2.0

    @Jae: I ran a quick screen last week with Bloomberg on US companies. The lowest quintile of companies had P/B ratios below 0.6, roughly.

  • http://bingoslotpot.com Bryanna Seppelt

    hi, thanks for the helpful bingo info mate, there’s so many options these days it’s hard to know where to look for relevant info, thanks.

  • Joe

    Hi Jae Jun,

    I noticed that the Excel Piotroski calculator isn’t working anymore. Parameter 3, 8, 9, are returning “#value!” errors. This is a new development since it worked fine a few weeks ago.

    Keep up the excellent contributions.

    Joe

  • http://www.oldschoolvalue.com Jae Jun

    @ Joe,
    Yes some things have changed with the data and I just haven’t had time to fix the free spreadsheets yet.

  • Chris

    Hi Jae Jun,

    On the Excel Petroski, I’m getting #VALUE! errors on parameters 3,5,6,8&9. Am I doing something wrong?

    Thanks!

    Chris

  • http://www.oldschoolvalue.com Jae Jun

    Chris,

    To get the latest working version, subscribe with your email and you will get a list of free spreadsheet downloads.

  • Michael Perline

    The Piotroski spreadsheet used to run. Then it wouldn’t. I reinstalled from the email. Now it won’t run because it says the macro is disabled due to it not being signed and/or security level is high. I try setting security to low (Tools>Macro>Security) but still get the same message every time. Any thoughts?

  • Dodge1664

    Hi Jae Jun,

    I can’t get the links to work because I have Windows installed on G: not C:, and I guess I need the password to change the links path. Is there any workaround?

    regards,

    Roger

  • http://www.oldschoolvalue.com Jae Jun

    a work around is to search and replace “C:\SMF\..” with “” which will find all instances of C:\SMF\.. and delete it without affecting anything.

  • http://www.oldschoolvalue.com Jae Jun

    @ Michael,

    Did you check whether the add-in is activated? Maybe it got unchecked somehow.

  • Mike
  • http://n/a Mike

    …point # 4 says “cash flow from operations greater than ROA”. I’m not sure this makes a whole lot of sense, given that Cash flow from operations is an absolute value (ie: $1,000,0000) whereas ROA is a ratio (15%). This is effectively an apples to oranges comparison….

  • http://www.oldschoolvalue.com Jae Jun

    @ Mike,
    You’re right, I wrote it incorrectly. It should be “Cash from Operations greater than Net Income”

  • http://www.oldschoolvalue.com/investment-tools/free-piotroski-score-spreadsheet/ David

    Get #VALUE! error on Piotroski Score Spreadsheet.
    Not working.

  • David

    Always get #VALUE! errors on Piotroski Score Spreadsheet.
    Not working.

  • http://www.oldschoolvalue.com Jae Jun

    I need to update the post. To get the latest working version, you need to subscribe with your email. Then the download list will be sent to you.

  • Raj

    Why do I get #VALUE! or #DIV/0! errors for some companies (free spreadsheet)? Graham investor website does report these values for those companies.

  • http://www.oldschoolvalue.com Jae Jun

    @ Raj,
    There was a change in the website it was getting data from which is why the version you downloaded didn’t work. You have to sign up with your email to receive the latest working version. I have changed the download instructions and method on the post.

  • Raj

    Jae,
    I did sign up on your website and use your emailed link to download the spreadsheet. The spreadsheet seems to work ok for many stocks but not for some – for e.g. LYB

    Thanks!

  • http://www.oldschoolvalue.com Jae Jun

    Yes the spreadsheets will not work for any financials or REIT’s. FAQ can also be found here http://www.oldschoolvalue.com/faq.php

  • Marco

    Hi Jae,
    just discovered your site and tried to utilized the Free version of F-Score XLS.
    It is HALF working. I mean ONLY 2.Operating Cash Flow, 5.LT Debt vs Asset, 6.Current Ratio and 7.Shares Outstanding fields are filled-in. The others contain ##### (#VALUE!). Please consider A) I’m utilizing Excel 2007, ITALIAN version and B) if I digit http://www.adfvn.com I’m re-directed to the Italian site (it.advfn.com). Any clue ??? Thanks

  • http://mustafademirkol.com Mustafa Demirkol

    Hello,

    I believe there is a fundamental problem with this model. Model compares the company with the previous financials and gives 1 point if there is an improvement.

    Let’s think about this case: Company 1st year net income 10$ second year 5$ third year 10$ again.

    this model will give 0 points for the second year and give 1 points for the third year.

    However in fact this company has a very volatile business… I think using this model to understand company’s sustainable and strong financial performance will mislead investors.

    Furthermore the company might have an excellent margins and cashflow compared to its industry and it reached its physical limits within the industry… This model will give 0 points because this company doesn’t improve anymore. In fact I don’t need the company to improve anymore because it has already a perfect financial structure intact.

  • http://www.oldschoolvalue.com Jae Jun

    you bring up a good point and its one of the reasons why you should look at the Piotroski score over a period of time instead of just a single period. Like most things, its a better idea to get an idea of how the company has been doing. If the dip is just over 1 year in a span of 10 then you can conclude that it was a temporary issue.

x
Receive Email Tips and Strategies on Achieving Higher Stock Returns

Bonus: FREE Set of 9 Investing Spreadsheets