원문 보기
welcome back and in this video i'll show you how to make joel greenblatt's magic formula in google sheets using fmp and web scraping as well as use his screening method to find stocks that fit his formula first let's take a look at who joel greenblatt is american academic hedge fund manager investor and writer value investor professor at wharton and columbia pretty good he's a hedge fund called gotham let's take a look at it whale wisdom gotham asset management currently 3.7 billion dollars assets under management take a look at some of their highest conviction positions okay looks interesting so greenblatt is popular for his magic formula which he discusses in his book the little book that beats the market he was also featured in jack schwager's market wizards hedge fund which i highly recommend reading next let's take a look at putting the magic formula to use back to google sheets open a new sheet first we'll use cell a1 as our variable today we'll use walmart so the joel greenblatt's magic formula okay merge center make a bit bigger okay this company is index fmp quote point back to a1 for one year close parentheses we're indexing the second column second row walmart center these two make this bigger 14 bold and this will be our only variable so we'll make it blue okay step one we need return on capital grab our formula center urge okay so for our formula we need ebit divided by networking capital plus net fixed assets and joel greenblatt discusses the magic formula as being good companies with cheap prices so for the return on capital we need ebit and so for this we're going to need the income statement and we'll do the quarterly here we'll go equals transpose fmp income statement call back to a1 do four quarters close parentheses enter okay there's our income statement okay before we move on we need to turn this into trailing 12 months so make a ttm column come down here equals sum double click the box okay now we can get our ebitda for this we're going to need our income before taxes plus our interest expense turn that into a number next we need networking capital and net fixed assets okay for this we're gonna need the balance sheet so we'll do that over here so we can just grab ctrl c ctrl v turn this into balance sheet okay for networking capital we're gonna grab our last quarter's net receivables plus our inventory and minus out accounts payable okay next we need net fixed assets for this we'll go equals and we'll take ppne we're assuming with pp e that depreciation has already been netted out so we'll only take this for now with that we have our return on capital and our roc will be ebit divided by networking capital plus net fixed assets we could turn this into a percentage put a box around it okay great next we need step two which is earnings yield and for this we'll need ebit or earnings before interest and taxes and we can just take it from the cell and we'll need our enterprise value or ev fmp gives that to us so we can use our api to call on that to do so we'll go index fmp key metrics we'll do ttm go back to cell one one year we're going to index 211.
that'll give us our enterprise value okay next we need our earnings yield which is ebit over ev turn that into a percentage okay pretty good let's drop a formula there next we can give ourselves an indicator or signal for each of these first we'll start over here collapse those cells in this case we'll do if our return on capital is greater than 15 percent then buy and if not sell okay center it big okay and 15 variable you can use whatever you like next we'll do our earnings yield merge and for this we can just copy it paste that over and we can reduce this to five percent and we'll add some conditional formatting to it this will be f both of these texting is exactly cell show red bi and green okay right next we can view show turn gridlines off next we can screen for stocks using joel greenblatt's method to do that we can use finviz and here i've already got the presets set up and what greenblatt calls for in this screener is countries in the u.s no adrs a low pe a market cap of large in this case over 10 billion and then return on assets over 25 and that'll narrow it down when we start to pull out return on capital we'll be able to target companies that are efficiently run so we can test this out sort by low pe we can copy this we'll bring this over to our spreadsheet paste it in here so we know walmart's not doing great take a look at some of these other names stld bye bye very nice mrna good generon biotech yup hey matt nice lamb research semiconductor and qualcomm okay that's all i got and we'll see you soon thank you bye
댓글 0