How to use Joel Greenblatt's Magic Formula to Beat the Market with Google Sheets

요약 보기

조엘 그린블랫의 매직 포뮬라를 구글 시트와 FMP API, 웹 스크래핑으로 구현하는 방법을 설명한 튜토리얼 영상이다. 핵심 결론: 영상은 특정 종목 매수 추천이 아니라 매직 포뮬라 계산 방법과 Finviz 스크리닝 절차를 다룬다. 그린블랫 소개: 미국 학자이자 헤지펀드 매니저로, 와튼·컬럼비아 교수이며 Gotham Asset Management를 운용한다(운용자산 약 37억 달러로 언급). 매직 포뮬라 개념: 좋은 기업을 싸게 사는 전략으로, 책 'The Little Book That Beats the Market'에서 소개되었다. 계산 1단계 – 자본수익률(ROC): EBIT ÷ (운전자본 + 순고정자산)으로 계산하며, 기준은 15% 이상이면 매수, 미만이면 매도로 설정한다. 계산 2단계 – 이익수익률(Earnings Yield): EBIT ÷ 기업가치(EV)로 계산하며, 기준은 5% 이상이면 매수, 미만이면 매도다. 데이터 처리: 분기 손익계산서를 TTM으로 합산하고, 대차대조표에서 운전자본(매출채권+재고-매입채무)과 순고정자산(PP&E)을 가져온다. 예시 종목: 월마트를 A1 변수로 넣어 계산 과정을 보여주었다. 스크리닝: Finviz에서 미국 상장(ADR 제외), 낮은 PER, 시가총액 100억 달러 이상, ROA 25% 이상 조건으로 필터링하고 PER 낮은 순으로 정렬한다. 언급 종목 예: STLD, MRNA, LRCX, QCOM 등이 스크리닝 결과로 나열되었으나 투자 추천은 아니다. 리스크/조건: 영상은 계산 방법 설명이며 수익을 보장하지 않는다. 대상 자산(삼성전자 등 지정 자산)에 대한 가격 방향 예측 발언은 없다.

다듬은 글

※ 자동 자막 원문(다듬기 전)

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

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