
                        
                            -- check the total rows in data table
SELECT count(*)
from "SP500Info"


                        
                            -- (Cross-sectional) Company info
select *
from "SP500Info"


                        
                            -- (Panel) Income Statement
select *
from "SP500Income"


                        
                            -- (Panel) Balance Sheet
select *
from "SP500BalanceSheet"


                        
                            -- (Panel) Cashflow Statement
select *
from "SP500CashFlow"


                        
                            -- (Cross-sectional) Company ranking - Linear Regression expected recommendation
select *
from "SP500LM"


                        
                            -- (Panel) Growth
with growth (date, rev, ticker, net_income, stock, assets, liab, div)
as
(select income."Date", income."Total Revenue", income."Ticker", income."Net Income",
balance."Common Stock", balance."Total Assets", balance."Total Liab", cashflow."Dividends Paid"
from "SP500Income" income
join "SP500BalanceSheet" balance
on income."Date" = balance."Date"
and income."Ticker" = balance."Ticker"
join "SP500CashFlow" cashflow
on income."Date" = cashflow."Date"
and income."Ticker" = cashflow."Ticker")

select date as "Date",
ticker as "Ticker",
round(((rev - lag(rev,1) over (partition by ticker order by ticker, date)) / lag(rev,1) over (partition by ticker order by ticker, date))::numeric,3) as "Revenue %", 
round((((net_income/stock) - lag((net_income/stock),1) over (partition by ticker order by ticker, date)) / lag((net_income/stock),1) over (partition by ticker order by ticker, date))::numeric,3) as "Earnings/Share %", 
round(((((assets-liab)/stock) - lag(((assets-liab)/stock),1) over (partition by ticker order by ticker, date)) / lag(((assets-liab)/stock),1) over (partition by ticker order by ticker, date))::numeric,3) as "Book Value/Share %", 
round((((ABS(div)/stock) - lag((ABS(div)/stock),1) over (partition by ticker order by ticker, date)) / lag((ABS(div)/stock),1) over (partition by ticker order by ticker, date))::numeric,3) as "Dividends/Share %"
from growth


                        
                            -- (Panel) Profitability
with profit (date, ticker, net_income, assets, capexp, ocf)
as
(select income."Date", income."Ticker", income."Net Income",
balance."Total Assets", cashflow."Capital Expenditures", cashflow."Total Cash From Operating Activities"
from "SP500Income" income
join "SP500BalanceSheet" balance
on income."Date" = balance."Date"
and income."Ticker" = balance."Ticker"
join "SP500CashFlow" cashflow
on income."Date" = cashflow."Date"
and income."Ticker" = cashflow."Ticker")

select date as "Date",
ticker as "Ticker",
round((((net_income/assets) - lag((net_income/assets),1) over (partition by ticker order by ticker, date)) / lag((net_income/assets),1) over (partition by ticker order by ticker, date))::numeric,3) as "Return on Assets %", 
ocf as "Operating Cash Flow", 
capexp as "Cap Spending",
ocf + capexp as "Free Cash Flow"
from profit


                        
                            -- (Panel) Financial Health
select "Date", "Ticker", "Long Term Debt", "Total Stockholder Equity", 
round(("Long Term Debt"/"Total Stockholder Equity")::numeric,1) as "Debt/Equity Ratio"
from "SP500BalanceSheet"


                        
                            -- (Cross-sectional) Valuation Ratios
select "symbol","sector",
round(("trailingPE")::numeric,1) as "Price/Earnings",
round(("priceToBook")::numeric,1) as "Price/Book",
round(("priceToSalesTrailing12Months")::numeric,1) as "Price/Sales",
round(("regularMarketPrice"/("operatingCashflow"/"sharesOutstanding"::float))::numeric,1) as "Price/Cash Flow"
from "SP500Info"

zip	sector	fullTimeEmployees	city	phone	state	country	website	address1	industry	ebitdaMargins	profitMargins	grossMargins	operatingCashflow	revenueGrowth	operatingMargins	ebitda	grossProfits	freeCashflow	currentPrice	earningsGrowth	currentRatio	returnOnAssets	debtToEquity	returnOnEquity	totalCash	totalDebt	totalRevenue	totalCashPerShare	revenuePerShare	quickRatio	exchange	shortName	longName	isEsgPopulated	symbol	messageBoardId	enterpriseToRevenue	enterpriseToEbitda	forwardEps	sharesOutstanding	bookValue	sharesShort	sharesPercentSharesOut	lastFiscalYearEnd	heldPercentInstitutions	netIncomeToCommon	trailingEps	priceToBook	heldPercentInsiders	nextFiscalYearEnd	mostRecentQuarter	shortRatio	sharesShortPreviousMonthDate	floatShares	beta	enterpriseValue	lastSplitDate	lastSplitFactor	earningsQuarterlyGrowth	priceToSalesTrailing12Months	dateShortInterest	pegRatio	forwardPE	shortPercentOfFloat	sharesShortPriorMonth	previousClose	regularMarketOpen	twoHundredDayAverage	trailingAnnualDividendYield	payoutRatio	regularMarketDayHigh	averageDailyVolume10Day	regularMarketPreviousClose	fiftyDayAverage	trailingAnnualDividendRate	open	averageVolume10days	dividendRate	exDividendDate	regularMarketDayLow	currency	trailingPE	regularMarketVolume	marketCap	averageVolume	dayLow	ask	askSize	volume	fiftyTwoWeekHigh	fiveYearAvgDividendYield	fiftyTwoWeekLow	bid	dividendYield	bidSize	dayHigh	regularMarketPrice	preMarketPrice	logo_url
55144-1000	Industrials	95000.0	Saint Paul	651 733 1110	MN	United States	https://www.3m.com	3M Center	Conglomerates	0.24558	0.11828	0.44526002	6016999936	-0.028	0.19131	8615999488	16579000000	3868499968	112.92	-0.947	1.467	0.08931	124.464	0.2938	2984000000	17195999232	35085000704	5.239	61.076	0.798	NYQ	3M Company	3M Company	True	MMM	finmb_289194	2.733	11.128	10.59	576252992	23.737	7765938	0.0136	1640908800	0.6758	4150000128	9.772	4.75713	0.00087	1703980800	1656547200	2.44	1655251200	569096981	0.945083	95876063232	1064880000	2:1	-0.949	1.8546525	1657843200	33.7	10.6628895	0.0137	9123534	110.5	111.69	146.6422	0.053303167	0.8296	113.09	3447060	110.5	131.5158	5.89	111.69	3447060	5.96	1652918400	110.91	USD	11.555464	1071946	65070485504	9686761	110.91	112.95	1300	1071946	186.3	3.15	110.39	112.89	0.0416	800	113.09	112.92	111.8	https://logo.clearbit.com/3m.com
53224-9508	Industrials	13700.0	Milwaukee	414 359 4000	WI	United States	https://www.aosmith.com	11270 West Park Place	Specialty Industrial Machinery	0.18995	0.13420999	0.35729	499500000	0.123	0.16991	732000000	1310900000	275400000	50.354	0.11	1.757	0.12667	18.471	0.28663	459400000	331100000	3853700096	2.974	24.446	1.164	NYQ	A.O. Smith Corporation	A. O. Smith Corporation	True	AOS	finmb_997111	2.502	13.174	2.98	133187000	11.523	5764708	0.037	1640908800	0.96606004	517200000	2.401	4.369869	0.00719	1703980800	1656547200	4.77	1655251200	126408040	1.139208	9643630592	1475712000	2:1	0.068	2.079675	1657843200	2.18	16.897316	0.0506	5318027	48.58	49.04	64.53735	0.020584602	0.3385	50.44	1047660	48.58	57.5746	1.0	49.04	1047660	1.12	1658966400	48.99	USD	20.972095	435942	8014443520	979165	48.99	50.39	900	435942	86.74	1.58	48.1	50.37	0.0177	800	50.44	50.354	48.96	https://logo.clearbit.com/aosmith.com
60064-6400	Healthcare	113000.0	North Chicago	224 667 6100	IL	United States	https://www.abbott.com	100 Abbott Park Road	Medical Devices	0.3037	0.1878	0.57895	NULL	0.101	0.22958	13832999936	25009000000	NULL	98.88	0.727	NULL	NULL	48.062	NULL	8158000128	17123999744	45547999232	4.659	25.821	NULL	NYQ	Abbott Laboratories	Abbott Laboratories	True	ABT	finmb_247483	4.386	14.441	5.41	1768290048	18.509	14216108	0.0081	1640908800	0.75545996	8525000192	3.192	5.3422656	0.0072899996	1703980800	1656547200	2.64	1655251200	1737705165	0.738612	199768555520	1357084800	10000:4798	0.697	3.8387747	1657843200	1.99	18.277264	0.0083	14018238	96.76	97.8	116.0536	0.016742455	0.3841	99.54	5798500	96.76	106.0136	1.62	97.8	5798500	1.88	1657756800	97.4	USD	30.977444	1913382	174848507904	4713676	97.4	98.71	1000	1913382	142.6	1.54	96.67	98.69	0.0173	900	99.54	98.88	97.86	https://logo.clearbit.com/abbott.com
60064-6400	Healthcare	50000.0	North Chicago	847 932 7900	IL	United States	https://www.abbvie.com	1 North Waukegan Road	Drug Manufacturers—General	0.51394004	0.22035	0.70599	NULL	0.045	0.37075	29474000896	38883000000	NULL	137.59	0.217	NULL	NULL	451.44	NULL	7571999744	73647996928	57349001216	4.285	32.391	NULL	NYQ	AbbVie Inc.	AbbVie Inc.	True	ABBV	finmb_141885706	5.575	10.847	13.82	1767879936	7.408	14029156	0.007900001	1640908800	0.71441	12563000320	2.913	18.573164	0.00107	1703980800	1656547200	2.22	1655251200	1765113249	0.748829	319704956928	NULL	NULL	0.206	4.2414446	1657843200	63.35	9.955861	0.007900001	13524712	134.21	135.88	146.2848	0.027270695	0.7618	138.648	6394350	134.21	141.2434	3.66	135.88	6394350	5.64	1657756800	135.3058	USD	47.23309	3091789	243242598400	5507111	135.3058	137.39	1300	3091789	175.91	4.28	106.86	137.37	0.039300002	900	138.648	137.59	135.88	https://logo.clearbit.com/abbvie.com
01923	Healthcare	2003.0	Danvers	978 646 1400	MA	United States	https://www.abiomed.com	22 Cherry Hill Drive	Medical Devices	0.27519	0.13229999	0.81763	285390016	0.119	0.24797001	283932992	843595000	200419504	253.11	0.056	7.053	0.100959994	0.665	0.096370004	758606976	9996000	1031753024	16.649	22.703	6.133	NMS	ABIOMED, Inc.	Abiomed, Inc.	False	ABMD	finmb_247589	12.214	44.384	5.22	45497500	29.371	1770832	0.0388	1648684800	0.9464	136504992	4.94	8.617684	0.02003	1711843200	1648684800	5.94	1655251200	44632610	1.321839	12602078208	970444800	2:1	0.061	11.161462	1657843200	3.16	48.488506	0.05	1723322	245.66	248.71	285.91156	NULL	0.0	255.805	294490	245.66	271.761	NULL	248.71	294490	NULL	NULL	245.77	USD	51.236843	188215	11515872256	261371	245.77	252.7	900	188215	379.3	NULL	219.85	252.13	NULL	900	255.805	253.11	245.66	https://logo.clearbit.com/abiomed.com
2	Technology	710000.0	Dublin	353 1 646 2000	NULL	Ireland	https://www.accenture.com	1 Grand Canal Square	Information Technology Services	0.16853	0.11123	0.32257	8187450880	0.218	0.152	10042649600	16364128000	6171557376	263.33	0.161	1.268	0.12833999	15.849	0.32139	6707890176	3488608000	59589931008	10.605	94.123	1.143	NYQ	Accenture plc	Accenture plc	True	ACN	finmb_972190	3.207	19.032	9.38	632001024	28.802	3811462	0.006	1630368000	0.77428	6628289024	8.45	9.142767	0.00127	1693440000	1653955200	1.47	1655251200	631820279	1.220519	191127404544	1325203200	10:1	0.153	2.7950792	1657843200	2.09	28.073559	0.006	3700144	257.3	260.27	314.3399	0.013369609	0.3676	263.6	2674640	257.3	292.5158	3.44	260.27	2674640	3.88	1657670400	258.68	USD	31.163313	743059	166558580736	1987490	258.68	262.86	1100	743059	417.37	1.43	254.27	262.76	0.0127	1000	263.6	263.33	259.24	https://logo.clearbit.com/accenture.com
28209	Basic Materials	6000.0	Charlotte	980 299 5700	NC	United States	https://www.albemarle.com	4250 Congress Street	Specialty Chemicals	0.28061	0.077589996	0.32919997	392484000	0.36	0.20938	1017600000	1008471000	-83904624	270.09	1.56	1.111	0.043720003	43.769	0.060739998	463324992	2641511936	3626394112	3.956	31.006	0.567	NYQ	Albemarle Corporation	Albemarle Corporation	True	ALB	finmb_18671	8.548	30.462	4.95	116976000	39.949	2155339	0.0184	1640908800	0.84903	281377984	3.52	6.76087	0.00176	1703980800	1648684800	1.56	1655251200	116674691	1.575138	30998439936	1172793600	2:1	1.648	8.712249	1657843200	0.25	54.563637	0.0209	2061176	264.44	270.75	229.81685	0.005823627	0.6603	272.5096	1177920	264.44	268.2414	1.54	270.75	1177920	1.58	1663200000	262.37	USD	76.73011	817224	31594047488	1248203	262.37	270.61	800	817224	308.24	1.36	169.93	270.04	0.0064999997	900	272.5096	270.09	270.66	https://logo.clearbit.com/albemarle.com
90404	Communication Services	9800.0	Santa Monica	310 255 2000	CA	United States	https://www.activisionblizzard.com	2701 Olympic Boulevard Building B	Electronic Gaming & Multimedia	0.37693	0.29834	0.73264	2212000000	-0.223	0.36403	3127000064	6486000000	1775624960	74.465	-0.367	5.45	0.07794	20.234	0.14927	11134999552	3608000000	8296000000	14.241	10.66	5.092	NMS	Activision Blizzard, Inc	Activision Blizzard, Inc.	True	ATVI	finmb_4222231	6.628	17.584	4.2	778889024	19.422	17749275	0.0227	1640908800	0.84643996	2475000064	2.82	3.8340538	0.0086199995	1703980800	1648684800	3.48	1655251200	711160293	0.524052	54984425472	1220832000	2:1	-0.362	6.9913177	1657843200	2.54	17.729761	0.0229	15712394	74.34	74.41	77.1765	0.0063223033	0.1492	74.79	6749400	74.34	78.4062	0.47	74.41	6749400	0.47	1649808000	74.11	USD	26.406027	2005322	57999970304	5224568	74.11	74.28	1200	2005322	86.9	0.58	56.4	74.26	0.0058999998	800	74.79	74.465	74.41	https://logo.clearbit.com/activisionblizzard.com
60601	Consumer Defensive	39218.0	Chicago	312 634 8100	IL	United States	https://www.adm.com	77 West Wacker Drive	Farm Products	0.051609997	0.03813	0.07389	2912999936	0.19	0.04082	4870000128	5987000000	-777875008	81.69	0.73	1.467	0.04269	50.788	0.15677	1001000000	12538000384	94364000256	1.786	166.868	0.357	NYQ	Archer-Daniels-Midland Company	Archer-Daniels-Midland Company	True	ADM	finmb_251704	0.617	11.957	4.39	559441024	37.283	6692824	0.0119	1640908800	0.82073	3598000128	3.681	2.1910791	0.00488	1703980800	1656547200	1.74	1655251200	557484086	0.814814	58228682752	999129600	105:100	0.736	0.4843027	1657843200	1.3	18.608202	0.013300001	7294910	80.45	81.36	81.5369	0.018023618	0.24290001	81.92	2557600	80.45	84.8658	1.45	81.36	2557600	1.6	1652745600	80.92	USD	22.192339	1188227	45700739072	2645522	80.92	81.55	1000	1188227	98.88	2.86	60.83	81.54	0.019299999	900	81.92	81.69	81.24	https://logo.clearbit.com/adm.com
95110-2704	Technology	25988.0	San Jose	408 536 6000	CA	United States	https://www.adobe.com	345 Park Avenue	Software—Infrastructure	0.39909	0.29288	0.87869	7279000064	0.144	0.36249	6662000128	13920000000	5786500096	282.9432	0.073	1.071	0.14571	33.307	0.35126	5298999808	4657999872	16693000192	11.323	35.195	0.933	NMS	Adobe Inc.	Adobe Inc.	True	ADBE	finmb_24321	11.46	28.714	13.67	475800000	28.28	5664676	0.0121	1638489600	0.8496	4888999936	11.486	10.005064	0.00423	1701561600	1654214400	1.81	1655251200	466544520	1.108131	191295160320	1116892800	2:1	0.056	8.06472	1657843200	2.1	20.698112	0.0121	6224616	275.2	278.2694	430.6262	NULL	0.0	283.04	5630310	275.2	377.0008	NULL	278.2694	5630310	NULL	1111622400	277.0	USD	24.633745	1429114	134624378880	3493993	277.0	282.47	1300	1429114	699.54	NULL	274.73	282.27	NULL	800	283.04	282.9432	277.95	https://logo.clearbit.com/adobe.com

Date	Research Development	Effect Of Accounting Charges	Income Before Tax	Minority Interest	Net Income	Selling General Administrative	Gross Profit	Ebit	Operating Income	Other Operating Expenses	Interest Expense	Extraordinary Items	Non Recurring	Other Items	Income Tax Expense	Total Revenue	Total Operating Expenses	Cost Of Revenue	Total Other Income Expense Net	Discontinued Operations	Net Income From Continuing Ops	Net Income Applicable To Common Shares	Ticker
2021-12-31	1977000000.0	NULL	7214000000.0	71000000.0	5921000000.0	6812000000.0	16579000000.0	7790000000.0	7790000000.0	NULL	-488000000.0	NULL	NULL	NULL	1285000000.0	35355000000.0	27565000000.0	18776000000.0	-576000000.0	NULL	5929000000.0	5921000000.0	MMM
2020-12-31	1862000000.0	NULL	6790000000.0	64000000.0	5449000000.0	6667000000.0	15685000000.0	7156000000.0	7156000000.0	NULL	-529000000.0	NULL	NULL	NULL	1337000000.0	32184000000.0	25028000000.0	16499000000.0	-366000000.0	NULL	5453000000.0	5449000000.0	MMM
2019-12-31	1874000000.0	NULL	5643000000.0	63000000.0	4517000000.0	6893000000.0	15072000000.0	6305000000.0	6305000000.0	NULL	-448000000.0	NULL	NULL	NULL	1114000000.0	32136000000.0	25831000000.0	17064000000.0	-662000000.0	NULL	4529000000.0	4517000000.0	MMM
2018-12-31	1816000000.0	NULL	7000000000.0	52000000.0	5349000000.0	6527000000.0	16110000000.0	7767000000.0	7767000000.0	NULL	-350000000.0	NULL	NULL	NULL	1637000000.0	32765000000.0	24998000000.0	16655000000.0	-767000000.0	NULL	5363000000.0	5349000000.0	MMM
2021-12-31	NULL	NULL	625600000.0	NULL	487100000.0	702000000.0	1310900000.0	608900000.0	608900000.0	NULL	-4300000.0	NULL	NULL	NULL	138500000.0	3538900000.0	2930000000.0	2228000000.0	16700000.0	NULL	487100000.0	487100000.0	AOS
2020-12-31	NULL	NULL	443900000.0	NULL	344900000.0	667200000.0	1108200000.0	441000000.0	441000000.0	NULL	-7300000.0	NULL	NULL	NULL	99000000.0	2895300000.0	2454300000.0	1787100000.0	2900000.0	NULL	344900000.0	344900000.0	AOS
2019-12-31	NULL	NULL	472100000.0	NULL	370000000.0	721000000.0	1180700000.0	459700000.0	459700000.0	NULL	-11000000.0	NULL	NULL	NULL	102100000.0	2992700000.0	2533000000.0	1812000000.0	12400000.0	NULL	370000000.0	370000000.0	AOS
2018-12-31	NULL	NULL	557800000.0	NULL	444200000.0	755200000.0	1305500000.0	550300000.0	550300000.0	NULL	-8400000.0	NULL	NULL	NULL	113600000.0	3187900000.0	2637600000.0	1882400000.0	7500000.0	NULL	444200000.0	444200000.0	AOS
2021-12-31	2738000000.0	NULL	8211000000.0	222000000.0	7071000000.0	11006000000.0	25009000000.0	9218000000.0	9218000000.0	NULL	-533000000.0	NULL	NULL	NULL	1140000000.0	43075000000.0	33857000000.0	18066000000.0	-1007000000.0	NULL	7071000000.0	7042000000.0	ABT
2020-12-31	2365000000.0	NULL	4968000000.0	219000000.0	4495000000.0	9591000000.0	19753000000.0	5665000000.0	5665000000.0	NULL	-546000000.0	NULL	NULL	NULL	497000000.0	34608000000.0	28943000000.0	14855000000.0	-697000000.0	24000000.0	4471000000.0	4473000000.0	ABT

Date	Intangible Assets	Capital Surplus	Total Liab	Total Stockholder Equity	Minority Interest	Other Current Liab	Total Assets	Common Stock	Other Current Assets	Retained Earnings	Other Liab	Good Will	Gains Losses Not Affecting Retained Earnings	Other Assets	Cash	Total Current Liabilities	Deferred Long Term Asset Charges	Short Long Term Debt	Other Stockholder Equity	Property Plant Equipment	Total Current Assets	Long Term Investments	Net Tangible Assets	Short Term Investments	Net Receivables	Long Term Debt	Inventory	Accounts Payable	Ticker	Deferred Long Term Liab
2021-12-31	5288000000.0	6429000000.0	31955000000.0	15046000000.0	71000000.0	2090000000.0	47072000000.0	9000000.0	229000000.0	45821000000.0	6180000000.0	13486000000.0	-37213000000.0	2346000000.0	4564000000.0	9035000000.0	581000000.0	1291000000.0	-6750000000.0	10287000000.0	15403000000.0	262000000.0	-3728000000.0	201000000.0	4770000000.0	16056000000.0	4985000000.0	2994000000.0	MMM	NULL
2020-12-31	5835000000.0	6162000000.0	34413000000.0	12867000000.0	64000000.0	2346000000.0	47344000000.0	9000000.0	193000000.0	43821000000.0	7774000000.0	13802000000.0	-37125000000.0	2226000000.0	4634000000.0	7948000000.0	871000000.0	794000000.0	-7721000000.0	10285000000.0	14982000000.0	214000000.0	-6770000000.0	404000000.0	4830000000.0	17989000000.0	4239000000.0	2561000000.0	MMM	NULL
2019-12-31	6379000000.0	5907000000.0	34533000000.0	10063000000.0	63000000.0	2092000000.0	44659000000.0	9000000.0	719000000.0	42135000000.0	7075000000.0	13444000000.0	-37988000000.0	1461000000.0	2353000000.0	9222000000.0	521000000.0	1841000000.0	-8139000000.0	10191000000.0	12971000000.0	213000000.0	-9760000000.0	98000000.0	4963000000.0	17518000000.0	4134000000.0	2228000000.0	MMM	NULL
2018-12-31	2657000000.0	5643000000.0	26652000000.0	9796000000.0	52000000.0	1852000000.0	36500000000.0	9000000.0	246000000.0	40636000000.0	5905000000.0	10051000000.0	-36492000000.0	1138000000.0	2853000000.0	7244000000.0	365000000.0	759000000.0	-6866000000.0	8738000000.0	13709000000.0	207000000.0	-2912000000.0	380000000.0	5123000000.0	13428000000.0	4366000000.0	2266000000.0	MMM	NULL
2021-12-31	364800000.0	545200000.0	1642200000.0	1832200000.0	NULL	73300000.0	3474400000.0	295200000.0	39100000.0	2826600000.0	311200000.0	627800000.0	-1834800000.0	90000000.0	443300000.0	1118800000.0	NULL	6800000.0	-331400000.0	639200000.0	1752600000.0	NULL	839600000.0	188100000.0	634400000.0	189900000.0	447700000.0	745900000.0	AOS	NULL
2020-12-31	323900000.0	520400000.0	1312400000.0	1848300000.0	NULL	54800000.0	3160700000.0	295400000.0	43300000.0	2509600000.0	285300000.0	546800000.0	-1477100000.0	89100000.0	573100000.0	886300000.0	NULL	6800000.0	-321200000.0	582900000.0	1618000000.0	NULL	977600000.0	116500000.0	585000000.0	106400000.0	300100000.0	595200000.0	AOS	NULL
2019-12-31	338400000.0	509000000.0	1391200000.0	1666800000.0	NULL	43300000.0	3058000000.0	295400000.0	56500000.0	2323400000.0	308800000.0	546000000.0	-1461000000.0	80900000.0	374000000.0	766500000.0	NULL	6800000.0	-348300000.0	592300000.0	1500400000.0	NULL	782400000.0	177400000.0	589500000.0	277200000.0	303000000.0	509600000.0	AOS	NULL
2018-12-31	293100000.0	496700000.0	1354500000.0	1717000000.0	NULL	43200000.0	3071500000.0	295500000.0	41500000.0	2102800000.0	347800000.0	513000000.0	-1178000000.0	86900000.0	259700000.0	785300000.0	NULL	NULL	-350800000.0	540000000.0	1638500000.0	NULL	910900000.0	385300000.0	647300000.0	221400000.0	304700000.0	543800000.0	AOS	NULL
2021-12-31	12739000000.0	NULL	39172000000.0	35802000000.0	222000000.0	1234000000.0	75196000000.0	24470000000.0	292000000.0	31528000000.0	7815000000.0	23231000000.0	-20196000000.0	4363000000.0	9799000000.0	13105000000.0	3972000000.0	754000000.0	-8374000000.0	10112000000.0	24239000000.0	512000000.0	-168000000.0	450000000.0	6487000000.0	17296000000.0	5157000000.0	4408000000.0	ABT	NULL
2020-12-31	14784000000.0	NULL	39545000000.0	32784000000.0	219000000.0	1658000000.0	72548000000.0	24145000000.0	90000000.0	27627000000.0	8209000000.0	23744000000.0	-18988000000.0	2784000000.0	6838000000.0	11907000000.0	2418000000.0	7000000.0	-8946000000.0	10130000000.0	20441000000.0	665000000.0	-5744000000.0	310000000.0	6414000000.0	18527000000.0	5012000000.0	3946000000.0	ABT	NULL

Date	Investments	Change To Liabilities	Total Cashflows From Investing Activities	Net Borrowings	Total Cash From Financing Activities	Issuance Of Stock	Net Income	Change In Cash	Repurchase Of Stock	Effect Of Exchange Rate	Total Cash From Operating Activities	Depreciation	Other Cashflows From Investing Activities	Dividends Paid	Change To Inventory	Change To Account Receivables	Other Cashflows From Financing Activities	Change To Netincome	Capital Expenditures	Ticker	Change To Operating Activities
2021-12-31	204000000.0	518000000.0	-1317000000.0	-1145000000.0	-6145000000.0	639000000.0	5921000000.0	-70000000.0	-2199000000.0	-62000000.0	7454000000.0	1915000000.0	31000000.0	-3420000000.0	-903000000.0	-122000000.0	-20000000.0	369000000.0	-1603000000.0	MMM	NULL
2020-12-31	232000000.0	252000000.0	-580000000.0	-1875000000.0	-5300000000.0	429000000.0	5449000000.0	2281000000.0	-368000000.0	48000000.0	8113000000.0	1911000000.0	10000000.0	-3388000000.0	-91000000.0	165000000.0	-98000000.0	295000000.0	-1501000000.0	MMM	NULL
2019-12-31	-192000000.0	-117000000.0	-6444000000.0	3249000000.0	-1124000000.0	547000000.0	4517000000.0	-500000000.0	-1407000000.0	-2000000.0	7070000000.0	1593000000.0	72000000.0	-3316000000.0	370000000.0	345000000.0	-197000000.0	157000000.0	-1699000000.0	MMM	NULL
2018-12-31	669000000.0	408000000.0	222000000.0	933000000.0	-6701000000.0	485000000.0	5349000000.0	-200000000.0	-4870000000.0	-160000000.0	6439000000.0	1488000000.0	9000000.0	-3193000000.0	-509000000.0	-305000000.0	-56000000.0	-126000000.0	-1577000000.0	MMM	NULL
2021-12-31	-67200000.0	142900000.0	-349900000.0	83500000.0	-421000000.0	32100000.0	487100000.0	-129800000.0	-366500000.0	NULL	641100000.0	77900000.0	NULL	-170100000.0	-109500000.0	-25500000.0	NULL	11900000.0	-75100000.0	AOS	34600000.0
2020-12-31	68600000.0	85600000.0	11800000.0	-170800000.0	-374800000.0	11400000.0	344900000.0	199100000.0	-56700000.0	NULL	562100000.0	80000000.0	NULL	-158700000.0	2900000.0	4500000.0	NULL	12700000.0	-56800000.0	AOS	28100000.0
2019-12-31	205300000.0	-35400000.0	33900000.0	62600000.0	-375800000.0	11400000.0	370000000.0	114300000.0	-287700000.0	NULL	456200000.0	78300000.0	NULL	-149200000.0	6300000.0	62400000.0	-1500000.0	13300000.0	-64400000.0	AOS	-28600000.0
2018-12-31	72500000.0	8800000.0	-12700000.0	-189000000.0	-523100000.0	900000.0	444200000.0	-86900000.0	-202600000.0	NULL	448900000.0	71900000.0	NULL	-130100000.0	-7700000.0	-54600000.0	-2300000.0	10100000.0	-85200000.0	AOS	-30800000.0
2021-12-31	-96000000.0	1288000000.0	-2008000000.0	-248000000.0	-5494000000.0	255000000.0	7071000000.0	2961000000.0	-2299000000.0	-70000000.0	10533000000.0	3538000000.0	26000000.0	-3202000000.0	-456000000.0	-383000000.0	NULL	695000000.0	-1885000000.0	ABT	-312000000.0
2020-12-31	-73000000.0	1766000000.0	-2215000000.0	-50000000.0	-2779000000.0	245000000.0	4495000000.0	2978000000.0	-403000000.0	71000000.0	7901000000.0	3327000000.0	19000000.0	-2560000000.0	-493000000.0	-924000000.0	-11000000.0	971000000.0	-2177000000.0	ABT	-627000000.0

symbol	priceToSales	priceToBook	dividendYield	debtToEquity	marketCap	CARG	CANSG	opMarg	intCov	sector	rating	id	lmPred
A	5.7772093	7.9246006	0.0063	59.918	37707845632.0	0.0874150759988666	0.0	0.206616929641497	18.3333333333333	Healthcare	2.0	1	2.30993123018623
AAL	0.19011787	0.0	0.0	0.0	7741029376.0	-0.124550834997855	0.0626425122277763	-0.0477915603659353	0.391111111111111	Industrials	2.8	2	2.2317852315898
AAP	0.9173845	3.0039387	0.031	136.984	10129602560.0	0.0469230022570291	0.0	0.0927561571931193	29.4319282368818	Consumer Cyclical	2.0	3	2.50982759656375
AAPL	6.015734	34.274002	0.0057	205.984	2331349614592.0	0.113028291077798	0.126278355993895	0.284134114695431	39.3338374291115	Technology	2.05	4	1.70614988745401
ABBV	4.2414446	18.573164	0.039300002	451.44	243242598400.0	0.197111448159498	0.0	0.399941628435602	9.40033016921172	Healthcare	2.58333333333333	5	2.5877914612132
ABC	0.12562306	0.0	0.0126	857.149	28851394560.0	0.0841031010931768	0.00827433295593427	0.0113850257445613	14.6079082303445	Healthcare	2.27272727272727	6	2.54588432325052
ABMD	11.161462	8.617684	0.0	0.665	11515872256.0	0.102701434855805	0.00294696178410181	0.325716441611146	0.0	Healthcare	2.66666666666667	7	2.26500997011814
ABT	3.8387747	5.3422656	0.0173	48.062	174848507904.0	0.120967625046106	0.0133979652907987	0.217796065814025	19.7617260787992	Healthcare	1.73333333333333	8	2.35723039007914
ACN	2.7950792	9.142767	0.0127	15.849	166558580736.0	0.0722194683331476	0.0	0.165826600040792	150.863107644725	Technology	2.26666666666667	9	2.35716776704713
ADBE	8.06472	10.005064	0.0	33.307	134624378880.0	0.203958519490086	0.0	0.436278268014811	63.9823008849558	Technology	1.96153846153846	10	2.15886702616469

S&P500 Screener Project¶

SQL: Identify Key Metrics ¶

Here are a few key metrics using SQL¶

Date	Ticker	Revenue %	Earnings/Share %	Book Value/Share %	Dividends/Share %
2018-10-31	A	NULL	NULL	NULL	NULL
2019-10-31	A	0.051	2.389	0.039	0.079
2020-10-31	A	0.034	-0.329	0.026	0.078
2021-10-31	A	0.184	0.683	0.106	0.063
2018-12-31	AAL	NULL	NULL	NULL	NULL
2019-12-31	AAL	0.028	0.493	-0.127	0.196
2020-12-31	AAL	-0.621	-4.513	37.797	-0.839
2021-12-31	AAL	0.724	-0.776	0.069	NULL
2018-12-29	AAP	NULL	NULL	NULL	NULL
2019-12-28	AAP	0.013	0.149	0.000	-0.036

Date	Ticker	Return on Assets %	Operating Cash Flow	Cap Spending	Free Cash Flow
2018-10-31	A	NULL	1087000000.0	-177000000.0	910000000.0
2019-10-31	A	2.063	1021000000.0	-155000000.0	866000000.0
2020-10-31	A	-0.341	921000000.0	-119000000.0	802000000.0
2021-10-31	A	0.513	1485000000.0	-188000000.0	1297000000.0
2018-12-31	AAL	NULL	3533000000.0	-3745000000.0	-212000000.0
2019-12-31	AAL	0.206	3815000000.0	-4366000000.0	-551000000.0
2020-12-31	AAL	-6.099	-6543000000.0	-2131000000.0	-8674000000.0
2021-12-31	AAL	-0.791	704000000.0	-412000000.0	292000000.0
2018-12-29	AAP	NULL	811028000.0	-193715000.0	617313000.0
2019-12-28	AAP	-0.077	866909000.0	-270129000.0	596780000.0

symbol	sector	Price/Earnings	Price/Book	Price/Sales	Price/Cash Flow
MMM	Industrials	11.6	4.8	1.9	10.8
AOS	Industrials	21.0	4.4	2.1	13.4
ABT	Healthcare	31.0	5.3	3.8	NULL
ABBV	Healthcare	47.2	18.6	4.2	NULL
ABMD	Healthcare	51.2	8.6	11.2	40.4
ACN	Technology	31.2	9.1	2.8	20.3
ALB	Basic Materials	76.7	6.8	8.7	80.5
ATVI	Communication Services	26.4	3.8	7.0	26.2
ADM	Consumer Defensive	22.2	2.2	0.5	15.7
ADBE	Technology	24.6	10.0	8.1	18.5