mgalgs/.gitignore

## .gitignore
/cnsfails202101b.txt
/cnsfails202102a.txt
/cnsfails202102b.txt
/cnsfails202103b.txt
/cnsfails202103a.txt

## ftd_stats.py
import sys
import pandas as pd


pd.set_option('max_columns', 20)
pd.set_option('display.width', 120)
pd.options.display.float_format = '{:,.0f}'.format

data = pd.read_csv(sys.argv[1], sep='|')
data["PRICE"] = pd.to_numeric(data["PRICE"], errors='coerce')
data["FAILED VALUE"] = (data["QUANTITY (FAILS)"] * data["PRICE"])

print("## Top FTDs (by failed value volume over the time range)\n")
print(data \
      .groupby(["SYMBOL"], as_index=False) \
      .sum("FAILED VALUE") \
      .sort_values("FAILED VALUE", ascending=False) \
      .head(20) \
      .to_markdown(index=False, floatfmt=",.0f"))

print("\n## Failed values stats\n")
print('```')
print(data[["SYMBOL", "FAILED VALUE"]] \
      .groupby('SYMBOL') \
      .sum("FAILED VALUE") \
      .describe())
print('```')

print("\n## GME FTDs\n")
print('```')
print(data[["SETTLEMENT DATE",
            "SYMBOL",
            "QUANTITY (FAILS)",
            "PRICE",
            "FAILED VALUE"]][data["SYMBOL"] == "GME"] \
      .sort_values("SETTLEMENT DATE") \
      .to_string(index=False))
print('```')

print("\n## IJR FTDs\n")
print('```')
print(data[["SETTLEMENT DATE",
            "SYMBOL",
            "QUANTITY (FAILS)",
            "PRICE",
            "FAILED VALUE"]][data["SYMBOL"] == "IJR"] \
      .sort_values("SETTLEMENT DATE") \
      .to_string(index=False))
print('```')

## output.txt
## Top FTDs (by failed value volume over the time range)

| SYMBOL   |   QUANTITY (FAILS) |   PRICE |   FAILED VALUE |
|:---------|-------------------:|--------:|---------------:|
| IWM      |          6,895,699 |   1,787 |  1,548,408,843 |
| PLTR     |         49,811,542 |     246 |  1,330,811,685 |
| FUTU     |          4,873,218 |   1,545 |    844,640,025 |
| WISH     |         35,515,202 |     205 |    733,258,211 |
| QQQ      |          2,164,158 |   2,950 |    708,732,901 |
| SPY      |          1,693,066 |   3,507 |    658,984,877 |
| ENB      |         15,169,585 |     314 |    524,182,677 |
| HYG      |          5,602,528 |     787 |    489,340,183 |
| CREE     |          3,783,570 |     595 |    443,525,361 |
| ASHR     |          9,444,819 |     400 |    429,684,254 |
| OPEN     |         12,956,619 |     250 |    415,215,102 |
| JNK      |          3,463,669 |     983 |    378,013,844 |
| LQD      |          2,761,217 |   1,198 |    365,748,149 |
| MUB      |          2,972,567 |   1,050 |    346,040,681 |
| TLT      |          2,256,099 |   1,290 |    324,099,376 |
| ARKK     |          2,240,411 |   1,316 |    315,177,035 |
| CHTR     |            518,535 |   3,650 |    314,356,645 |
| ACWI     |          2,880,669 |     859 |    274,275,164 |
| XLV      |          2,352,954 |     919 |    270,886,370 |
| PSLV     |         26,763,937 |      90 |    266,292,493 |

## Failed values stats

       FAILED VALUE
count        11,911
mean      3,084,073
std      28,211,260
min               0
25%          14,198
50%          99,079
75%         707,851
max   1,548,408,843

## GME FTDs

SETTLEMENT DATE SYMBOL  QUANTITY (FAILS)  PRICE  FAILED VALUE
       20210216    GME             5,218     52       273,423
       20210217    GME            52,861     50     2,617,148
       20210218    GME             8,435     46       387,504
       20210219    GME            16,734     41       680,906
       20210222    GME             1,910     41        77,527
       20210223    GME            14,856     46       683,376
       20210224    GME           173,307     45     7,793,616
       20210225    GME            29,072     92     2,666,193
       20210226    GME           298,018    109    32,403,497

## XRT FTDs

SETTLEMENT DATE SYMBOL  QUANTITY (FAILS)  PRICE  FAILED VALUE
       20210216    XRT           120,072     80     9,606,961
       20210217    XRT             2,191     80       174,557
       20210218    XRT               463     79        36,600
       20210219    XRT            12,774     78       996,244
       20210223    XRT            29,834     79     2,363,449
       20210224    XRT           116,334     78     9,074,052
       20210225    XRT            22,765     82     1,869,689
       20210226    XRT            36,065     80     2,871,135

## post.md

      
    Raw
  

              post.md
            
          
    The SEC's FTD data set has been updated to include the second half of
March. A few interesting slices of the new dataset as well as the beginning
of March dataset are below. GME and IJR aren't present in the list of
highest "carried" volume of FTDs this time, probably due to the deep ITM
calls and other shenanigans HFs appear to be using to reset the FTD count.
I plan on making these posts every time there's a new FTD data dump so that
we can spot anomalies in the FTD trends for GME.
My previous FTD posts:

Second half of Feb
First half of Feb
Second half of Jan

(I didn't post for the first half of March so I'm including that below as well.)
If you'd like to see something else or spot an error please feel free to
make a suggestion on the script that generates this
report.
An important note from the SEC's website about the dataset:

Fails to deliver on a given day are a cumulative number of all fails
outstanding until that day, plus new fails that occur that day, less fails
that settle that day. The figure is not a daily amount of fails, but a
combined figure that includes both new fails on the reporting day as well
as existing fails.

This is an incredibly stupid way to report this number, almost suspiciously
obtuse... (Unless I'm missing something, someone please help me out
here...)
The upshot is that we can't just sum up the "Failed value" column to get a
total FTD count since we might be double-counting fails that remain
outstanding for a few days.
So all we can do is assume that there will be a similar ratio of these
multi-day FTD carries across different symbols, then we can compare them to
get a sense for relative FTD volume. If the similar ratio assumption
turns out to be wrong then analysis on this dataset is pointless, unless
I'm missing something...
First half of March

Top FTDs (by failed value volume over the time range)


SYMBOL
QUANTITY (FAILS)
PRICE
FAILED VALUE


QQQ
17,539,229
3,119
5,453,300,281


IEMG
27,877,784
585
1,811,350,491


SPY
4,284,327
3,850
1,657,807,761


SNOW
5,421,712
1,938
1,209,116,967


ASZU
85,017,239
41
871,413,691


DASH
5,445,366
1,504
769,742,304


VOO
2,111,140
2,842
758,941,181


RBLX
10,203,610
74
754,046,779


HYG
7,206,700
779
622,841,457


MCHI
6,703,735
858
572,723,679


APPH
18,515,329
236
375,210,971


JNK
3,396,685
973
367,582,512


XHB
5,782,826
636
360,137,875


IGV
1,025,018
3,099
352,910,090


TSLA
526,887
6,557
348,458,428


BBIN
6,061,636
570
344,632,135


LQD
2,452,151
1,175
318,433,878


BHP
4,093,265
767
314,113,790


ARKK
2,400,905
1,243
310,302,805


NCLH
10,183,588
304
301,365,189


Failed values stats

       FAILED VALUE
count        11,975
mean      4,175,218
std      60,253,958
min               0
25%          14,641
50%         108,782
75%         785,846
max   5,453,300,281

GME FTDs

SETTLEMENT DATE SYMBOL  QUANTITY (FAILS)  PRICE  FAILED VALUE
       20210301    GME            82,708    102     8,414,712
       20210302    GME            26,373    120     3,175,309
       20210303    GME            15,394    118     1,819,263
       20210304    GME            20,176    124     2,505,456
       20210305    GME            33,363    132     4,415,593
       20210308    GME             1,907    138       262,670
       20210309    GME            17,382    194     3,380,799
       20210310    GME            22,839    247     5,638,949
       20210311    GME            10,818    265     2,866,770
       20210312    GME           155,658    260    40,471,080

IJR FTDs

SETTLEMENT DATE SYMBOL  QUANTITY (FAILS)  PRICE  FAILED VALUE
       20210301    IJR               123    105        12,926
       20210302    IJR           296,623    108    32,136,136
       20210303    IJR               428    106        45,565
       20210304    IJR               217    107        23,137
       20210305    IJR            10,649    105     1,114,631
       20210308    IJR            17,957    107     1,922,297
       20210309    IJR             1,209    109       131,902
       20210310    IJR             1,090    110       120,085
       20210311    IJR             1,367    112       153,719

Second half of March

Top FTDs (by failed value volume over the time range)


SYMBOL
QUANTITY (FAILS)
PRICE
FAILED VALUE


SPY
14,661,585
4,723
5,784,404,934


QQQ
10,794,598
4,107
3,409,889,088


EEM
22,826,662
694
1,219,908,252


ARKK
5,694,276
1,567
649,107,869


IWM
2,819,288
2,441
618,148,553


CHPT
19,704,321
302
483,206,976


BNFT
32,081,855
72
471,711,041


VTI
1,808,012
2,677
371,432,050


INMD
4,674,418
773
328,537,577


VOO
882,120
2,535
319,178,767


DISCA
6,370,030
711
310,384,026


XBI
2,131,545
1,428
309,391,888


TLT
2,208,491
1,367
301,886,024


HYG
3,316,375
1,124
286,748,856


NEM
4,558,562
494
277,738,518


SLG
3,821,827
736
274,701,230


FUTU
1,968,331
1,691
272,082,661


IEMG
4,182,773
709
270,763,750


JNK
2,407,754
1,295
259,784,910


VIAC
3,080,197
902
259,686,367


Failed values stats

       FAILED VALUE
count        12,426
mean      4,513,376
std      64,143,120
min               0
25%          17,585
50%         133,243
75%       1,020,899
max   5,784,404,934

GME FTDs

SETTLEMENT DATE SYMBOL  QUANTITY (FAILS)  PRICE  FAILED VALUE
       20210315    GME            46,344    264    12,257,988
       20210316    GME           140,554    220    30,941,558
       20210317    GME            47,597    208     9,908,267
       20210318    GME            32,220    210     6,760,078
       20210319    GME               637    202       128,515
       20210322    GME            17,163    200     3,437,234
       20210323    GME            83,058    194    16,153,950
       20210324    GME            38,387    182     6,976,837
       20210325    GME            20,295    120     2,442,300
       20210326    GME            62,109    184    11,412,529
       20210329    GME            86,859    181    15,721,479
       20210330    GME            17,841    181     3,234,573
       20210331    GME            14,031    194     2,728,468

IJR FTDs

SETTLEMENT DATE SYMBOL  QUANTITY (FAILS)  PRICE  FAILED VALUE
       20210315    IJR               215    115        24,727
       20210316    IJR           362,514    115    41,612,982
       20210324    IJR                54    106         5,724
       20210325    IJR               100    104        10,415
       20210326    IJR             7,712    107       822,793
       20210329    IJR           162,532    109    17,763,122
       20210330    IJR           218,376    107    23,276,698
       20210331    IJR           106,230    108    11,486,650
	/cnsfails202101b.txt
	/cnsfails202102a.txt
	/cnsfails202102b.txt
	/cnsfails202103b.txt
	/cnsfails202103a.txt
	import sys
	import pandas as pd


	pd.set_option('max_columns', 20)
	pd.set_option('display.width', 120)
	pd.options.display.float_format = '{:,.0f}'.format

	data = pd.read_csv(sys.argv[1], sep='\|')
	data["PRICE"] = pd.to_numeric(data["PRICE"], errors='coerce')
	data["FAILED VALUE"] = (data["QUANTITY (FAILS)"] * data["PRICE"])

	print("## Top FTDs (by failed value volume over the time range)\n")
	print(data \
	.groupby(["SYMBOL"], as_index=False) \
	.sum("FAILED VALUE") \
	.sort_values("FAILED VALUE", ascending=False) \
	.head(20) \
	.to_markdown(index=False, floatfmt=",.0f"))

	print("\n## Failed values stats\n")
	print('```')
	print(data[["SYMBOL", "FAILED VALUE"]] \
	.groupby('SYMBOL') \
	.sum("FAILED VALUE") \
	.describe())
	print('```')

	print("\n## GME FTDs\n")
	print('```')
	print(data[["SETTLEMENT DATE",
	"SYMBOL",
	"QUANTITY (FAILS)",
	"PRICE",
	"FAILED VALUE"]][data["SYMBOL"] == "GME"] \
	.sort_values("SETTLEMENT DATE") \
	.to_string(index=False))
	print('```')

	print("\n## IJR FTDs\n")
	print('```')
	print(data[["SETTLEMENT DATE",
	"SYMBOL",
	"QUANTITY (FAILS)",
	"PRICE",
	"FAILED VALUE"]][data["SYMBOL"] == "IJR"] \
	.sort_values("SETTLEMENT DATE") \
	.to_string(index=False))
	print('```')
	## Top FTDs (by failed value volume over the time range)

	\| SYMBOL \| QUANTITY (FAILS) \| PRICE \| FAILED VALUE \|
	\|:---------\|-------------------:\|--------:\|---------------:\|
	\| IWM \| 6,895,699 \| 1,787 \| 1,548,408,843 \|
	\| PLTR \| 49,811,542 \| 246 \| 1,330,811,685 \|
	\| FUTU \| 4,873,218 \| 1,545 \| 844,640,025 \|
	\| WISH \| 35,515,202 \| 205 \| 733,258,211 \|
	\| QQQ \| 2,164,158 \| 2,950 \| 708,732,901 \|
	\| SPY \| 1,693,066 \| 3,507 \| 658,984,877 \|
	\| ENB \| 15,169,585 \| 314 \| 524,182,677 \|
	\| HYG \| 5,602,528 \| 787 \| 489,340,183 \|
	\| CREE \| 3,783,570 \| 595 \| 443,525,361 \|
	\| ASHR \| 9,444,819 \| 400 \| 429,684,254 \|
	\| OPEN \| 12,956,619 \| 250 \| 415,215,102 \|
	\| JNK \| 3,463,669 \| 983 \| 378,013,844 \|
	\| LQD \| 2,761,217 \| 1,198 \| 365,748,149 \|
	\| MUB \| 2,972,567 \| 1,050 \| 346,040,681 \|
	\| TLT \| 2,256,099 \| 1,290 \| 324,099,376 \|
	\| ARKK \| 2,240,411 \| 1,316 \| 315,177,035 \|
	\| CHTR \| 518,535 \| 3,650 \| 314,356,645 \|
	\| ACWI \| 2,880,669 \| 859 \| 274,275,164 \|
	\| XLV \| 2,352,954 \| 919 \| 270,886,370 \|
	\| PSLV \| 26,763,937 \| 90 \| 266,292,493 \|

	## Failed values stats

	FAILED VALUE
	count 11,911
	mean 3,084,073
	std 28,211,260
	min 0
	25% 14,198
	50% 99,079
	75% 707,851
	max 1,548,408,843

	## GME FTDs

	SETTLEMENT DATE SYMBOL QUANTITY (FAILS) PRICE FAILED VALUE
	20210216 GME 5,218 52 273,423
	20210217 GME 52,861 50 2,617,148
	20210218 GME 8,435 46 387,504
	20210219 GME 16,734 41 680,906
	20210222 GME 1,910 41 77,527
	20210223 GME 14,856 46 683,376
	20210224 GME 173,307 45 7,793,616
	20210225 GME 29,072 92 2,666,193
	20210226 GME 298,018 109 32,403,497

	## XRT FTDs

	SETTLEMENT DATE SYMBOL QUANTITY (FAILS) PRICE FAILED VALUE
	20210216 XRT 120,072 80 9,606,961
	20210217 XRT 2,191 80 174,557
	20210218 XRT 463 79 36,600
	20210219 XRT 12,774 78 996,244
	20210223 XRT 29,834 79 2,363,449
	20210224 XRT 116,334 78 9,074,052
	20210225 XRT 22,765 82 1,869,689
	20210226 XRT 36,065 80 2,871,135
SYMBOL	QUANTITY (FAILS)	PRICE	FAILED VALUE
QQQ	17,539,229	3,119	5,453,300,281
IEMG	27,877,784	585	1,811,350,491
SPY	4,284,327	3,850	1,657,807,761
SNOW	5,421,712	1,938	1,209,116,967
ASZU	85,017,239	41	871,413,691
DASH	5,445,366	1,504	769,742,304
VOO	2,111,140	2,842	758,941,181
RBLX	10,203,610	74	754,046,779
HYG	7,206,700	779	622,841,457
MCHI	6,703,735	858	572,723,679
APPH	18,515,329	236	375,210,971
JNK	3,396,685	973	367,582,512
XHB	5,782,826	636	360,137,875
IGV	1,025,018	3,099	352,910,090
TSLA	526,887	6,557	348,458,428
BBIN	6,061,636	570	344,632,135
LQD	2,452,151	1,175	318,433,878
BHP	4,093,265	767	314,113,790
ARKK	2,400,905	1,243	310,302,805
NCLH	10,183,588	304	301,365,189
SYMBOL	QUANTITY (FAILS)	PRICE	FAILED VALUE
SPY	14,661,585	4,723	5,784,404,934
QQQ	10,794,598	4,107	3,409,889,088
EEM	22,826,662	694	1,219,908,252
ARKK	5,694,276	1,567	649,107,869
IWM	2,819,288	2,441	618,148,553
CHPT	19,704,321	302	483,206,976
BNFT	32,081,855	72	471,711,041
VTI	1,808,012	2,677	371,432,050
INMD	4,674,418	773	328,537,577
VOO	882,120	2,535	319,178,767
DISCA	6,370,030	711	310,384,026
XBI	2,131,545	1,428	309,391,888
TLT	2,208,491	1,367	301,886,024
HYG	3,316,375	1,124	286,748,856
NEM	4,558,562	494	277,738,518
SLG	3,821,827	736	274,701,230
FUTU	1,968,331	1,691	272,082,661
IEMG	4,182,773	709	270,763,750
JNK	2,407,754	1,295	259,784,910
VIAC	3,080,197	902	259,686,367