Append

[Bashar Fin]

Hi All, I have six Excel files that I need to append together. When attempting to append them using the Stata menu, I encountered an issue where the values for certain variables were changed and did not match the original files. I am seeking a method to append these files while ensuring that variables' types, formats, and values are accurately preserved.
Example 1

Date	Symbol	Short Volume	Short Exempt Volume	Total Volume	Market
1/26/2024	A	1226	0	2997	B
1/26/2024	A	187187	22	361481	Q
1/26/2024	A	1809	0	3149	N
1/26/2024	AA	893	0	6872	B
1/26/2024	AA	1015095	2532	2238603	Q
1/26/2024	AA	19457	109	42222	N
1/26/2024	AAA	6885	0	6924	Q
1/26/2024	AAAU	254616	0	481912	Q
1/26/2024	AAAU	27362	0	68233	N
1/26/2024	AABB	1373708	0	2712493	O
1/26/2024	AACAY	55	0	7795	O
1/26/2024	AACG	1550	1	3207	Q
1/26/2024	AACI	3	0	3	Q
1/26/2024	AACIU	4	0	4	Q
1/26/2024	AACIW	1800	0	2500	Q
1/26/2024	AACT	39	0	23341	Q
1/26/2024	AACT	1	0	1	N
1/26/2024	AACT/WS	2000	0	2000	Q
1/26/2024	AADI	51197	0	73895	Q
1/26/2024	AADI	1345	0	2533	N
1/26/2024	AADR	2	0	99	Q
1/26/2024	AAGFF	3500	0	87108	O
1/26/2024	AAGIY	72881	0	267760	O
1/26/2024	AAGR	450	0	550	B
1/26/2024	AAGR	7060	2190	13133	Q
1/26/2024	AAGR	2500	0	19750	N
1/26/2024	AAIGF	175573	0	315391	O
1/26/2024	AAL	13052	0	67380	B
1/26/2024	AAL	6504558	68511	19654319	Q
1/26/2024	AAL	354572	22064	781788	N

Example 2

Date	Symbol	Short Exempt Volume	Short Volume	Total Volume	Market
20240126	A	0	281	405	A
20240126	AA	0	1577	3993	A
20240126	AAAU	0	700	700	A
20240126	AADI	0	331	331	A
20240126	AAL	0	28525	146626	A
20240126	AAMC	0	303	1726	A
20240126	AAN	0	38	157	A
20240126	AAOI	0	635	1601	A
20240126	AAON	0	117	117	A
20240126	AAP	0	0	404	A
20240126	AAPL	100	20225	40249	A
20240126	AAPU	0	0	50	A
20240126	AAT	0	5	5	A
20240126	AAU	820	33492	44060	A
20240126	AAXJ	0	464	664	A
20240126	AB	0	45	225	A
20240126	ABAT	0	205	626	A
20240126	ABBV	0	1011	2284	A
20240126	ABCB	0	630	635	A
20240126	ABCL	0	2595	3325	A
20240126	ABEV	0	6691	12291	A
20240126	ABG	0	160	201	A
20240126	ABIO	0	100	700	A
20240126	ABM	0	415	434	A
20240126	ABNB	3	11793	15880	A
20240126	ABOS	0	616	816	A
20240126	ABR	0	1686	2561	A
20240126	ABR PRD	0	0	122	A
20240126	ABR PRF	0	200	714	A
20240126	ABSI	0	2175	2408	A

Example 3

Date	Symbol	Short Exempt Volume	Short Volume	Total Volume	Market
20240126	A	0	5323	6088	C
20240126	AA	0	11230	15201	C
20240126	AAA	0	700	700	C
20240126	AACG	0	100	100	C
20240126	AADI	0	1758	1758	C
20240126	AAGR	0	0	978	C
20240126	AAL	102	76888	169647	C
20240126	AAN	0	526	927	C
20240126	AAOI	0	587	3503	C
20240126	AAON	0	237	238	C
20240126	AAP	0	220	555	C
20240126	AAPD	0	104	104	C
20240126	AAPL	12	20835	43979	C
20240126	AAT	0	172	426	C
20240126	AAXJ	0	0	10	C
20240126	AB	0	170	170	C
20240126	ABAT	0	317	611	C
20240126	ABBV	0	2896	6461	C
20240126	ABCB	0	603	719	C
20240126	ABCL	0	2741	3143	C
20240126	ABEO	0	6	6	C
20240126	ABEV	0	30175	69431	C
20240126	ABG	0	211	239	C
20240126	ABL	0	400	400	C
20240126	ABM	0	287	302	C
20240126	ABNB	100	18012	21004	C
20240126	ABOS	0	118	15551	C
20240126	ABR	0	21171	25157	C
20240126	ABSI	11	623	740	C
20240126	ABT	0	2617	3068	C
20240126	ABUS	0	800	3297	C
20240126	ABVC	0	282	282	C
20240126	ACA	0	193	194	C
20240126	ACAD	0	300	1053	C

Example 4

Date	Symbol	Short Exempt Volume	Short Volume	Total Volume	Market
20240126	A	0	3466	9084	M
20240126	AA	0	931	1131	M
20240126	AADI	0	7	7	M
20240126	AAL	0	17855	146794	M
20240126	AAN	0	254	304	M
20240126	AAOI	0	3874	9526	M
20240126	AAON	0	1616	2856	M
20240126	AAP	0	5572	7020	M
20240126	AAPL	0	46134	697739	M
20240126	AAT	0	998	1493	M
20240126	AB	0	2699	4170	M
20240126	ABAT	0	829	1280	M
20240126	ABBV	60	10762	1003227	M
20240126	ABCB	0	3900	7148	M
20240126	ABCL	0	149	1266	M
20240126	ABEO	0	200	400	M
20240126	ABEV	0	100	2600	M
20240126	ABG	0	912	1511	M
20240126	ABIO	0	0	100	M
20240126	ABL	0	52	52	M
20240126	ABM	0	1195	1426	M
20240126	ABNB	0	39450	45614	M
20240126	ABOS	0	196	196	M
20240126	ABR	0	448	548	M
20240126	ABSI	0	545	1045	M
20240126	ABT	0	9136	29615	M
20240126	ABUS	0	8450	8450	M
20240126	ABVC	0	100	100	M
20240126	ABVX	0	698	998	M
20240126	ACA	0	598	1059	M
20240126	ACAD	0	1589	1871	M
20240126	ACB	1300	6000	13400	M
20240126	ACCD	0	853	1110	M
20240126	ACCO	0	85	93	M
20240126	ACDC	0	228	751	M

Example 5

Date	Symbol	Short Exempt Volume	Short Volume	Total Volume	Market
20240126	A	2	226418	408482	N
20240126	AA	5080	328871	835567	N
20240126	AAA	0	100	100	N
20240126	AAAU	0	19510	20010	N
20240126	AACT	0	0	1200	N
20240126	AACT WS	0	0	29499	N
20240126	AADI	0	1262	2264	N
20240126	AAGR	0	0	900	N
20240126	AAL	600	482368	858528	N
20240126	AAN	1	29081	84888	N
20240126	AAOI	0	8494	26053	N
20240126	AAON	0	1277	1389	N
20240126	AAP	173	119574	236126	N
20240126	AAPB	0	940	1029	N
20240126	AAPD	0	7197	7795	N
20240126	AAPL	1331	550350	976669	N
20240126	AAPU	0	30	2730	N
20240126	AAT	61	42117	94935	N
20240126	AAU	0	225	225	N
20240126	AAXJ	0	6504	11674	N
20240126	AB	0	14713	26754	N
20240126	ABAT	0	1939	2653	N
20240126	ABBV	3063	640583	1147251	N
20240126	ABCB	0	4335	6200	N
20240126	ABCL	0	36229	48807	N
20240126	ABEO	0	218	751	N
20240126	ABEV	31	267626	582768	N
20240126	ABG	14	26852	39341	N
20240126	ABLLL	0	0	200	N
20240126	ABM	188	54926	147857	N
20240126	ABNB	460	120998	160331	N
20240126	ABOS	0	11374	19056	N
20240126	ABR	2896	321765	598943	N
20240126	ABR PRD	0	154	1676	N
20240126	ABR PRE	0	499	1409	N
20240126	ABR PRF	0	374	12304	N
20240126	ABSI	0	26258	30983	N
20240126	ABT	1258	993038	1476874	N
20240126	ABUS	0	43293	43953	N
20240126	ABVC	0	2365	3861	N

Example 6

Date	Symbol	Short Exempt Volume	Short Volume	Total Volume	Market
20240126	A	0	28005	34446	P
20240126	AA	400	72653	186982	P
20240126	AAA	0	1954	2153	P
20240126	AAAU	0	202435	211614	P
20240126	AACIW	0	2568	2968	P
20240126	AADI	0	5193	5897	P
20240126	AAGR	25	425	2730	P
20240126	AAGRW	0	4447	4447	P
20240126	AAL	700	1202837	2631350	P
20240126	AAMC	0	305	1632	P
20240126	AAME	0	16	202	P
20240126	AAN	0	1552	4547	P
20240126	AAOI	170	67570	154946	P
20240126	AAON	100	5949	8874	P
20240126	AAP	0	13449	26671	P
20240126	AAPB	0	574	890	P
20240126	AAPD	0	5878	8821	P
20240126	AAPL	4631	1141342	2290608	P
20240126	AAPU	0	1022	2538	P
20240126	AAPX	0	10	31	P
20240126	AAPY	0	20	52	P
20240126	AAT	101	3326	7461	P
20240126	AAU	0	60	268	P
20240126	AAXJ	0	31045	45499	P
20240126	AB	0	3406	4181	P
20240126	ABAT	0	2625	5235	P
20240126	ABBV	129	90467	137494	P
20240126	ABCB	105	10966	15131	P
20240126	ABCL	0	131535	169117	P
20240126	ABEO	0	193	1247	P
20240126	ABEQ	0	19	31	P
20240126	ABEV	0	50489	86427	P
20240126	ABG	0	2178	2894	P
20240126	ABIO	0	400	1384	P
20240126	ABL	0	260	326	P
20240126	ABLLW	0	436	10071	P
20240126	ABLV	0	0	1	P
20240126	ABM	12	5607	12663	P
20240126	ABNB	339	504031	716203	P
20240126	ABOS	0	27710	38876	P

[Andrew Musau]

Use dataex to present samples of the datasets. See FAQ Advice #12 for details.

[Bashar Fin]

Dear Andrew Musau , thank you for your reply.
* Example generated by -dataex-. For more info, type help dataex
clear
input int Date str9 Symbol long(ShortVolume ShortExemptVolume TotalVolume) str1 Market
23401 "A" 1226 0 2997 "B"
23401 "A" 187187 22 361481 "Q"
23401 "A" 1809 0 3149 "N"
23401 "AA" 893 0 6872 "B"
23401 "AA" 1015095 2532 2238603 "Q"
23401 "AA" 19457 109 42222 "N"
23401 "AAA" 6885 0 6924 "Q"
23401 "AAAU" 254616 0 481912 "Q"
23401 "AAAU" 27362 0 68233 "N"
23401 "AABB" 1373708 0 2712493 "O"
23401 "AACAY" 55 0 7795 "O"
23401 "AACG" 1550 1 3207 "Q"
23401 "AACI" 3 0 3 "Q"
23401 "AACIU" 4 0 4 "Q"
23401 "AACIW" 1800 0 2500 "Q"
23401 "AACT" 39 0 23341 "Q"
23401 "AACT" 1 0 1 "N"
23401 "AACT/WS" 2000 0 2000 "Q"
23401 "AADI" 51197 0 73895 "Q"
23401 "AADI" 1345 0 2533 "N"
23401 "AADR" 2 0 99 "Q"
23401 "AAGFF" 3500 0 87108 "O"
23401 "AAGIY" 72881 0 267760 "O"
23401 "AAGR" 450 0 550 "B"
23401 "AAGR" 7060 2190 13133 "Q"
23401 "AAGR" 2500 0 19750 "N"
23401 "AAIGF" 175573 0 315391 "O"
23401 "AAL" 13052 0 67380 "B"
23401 "AAL" 6504558 68511 19654319 "Q"
23401 "AAL" 354572 22064 781788 "N"
23401 "AAMC" 328 0 1783 "Q"
23401 "AAME" 1032 0 1191 "Q"
23401 "AAME" 59 0 59 "N"
23401 "AAN" 2000 0 2300 "B"
23401 "AAN" 8351 0 43109 "Q"
23401 "AAN" 413 0 536 "N"
23401 "AAOI" 1676 0 13265 "B"
23401 "AAOI" 294351 1859 903195 "Q"
23401 "AAOI" 6105 94 30677 "N"
23401 "AAON" 43650 22 111114 "Q"
23401 "AAON" 1179 0 1588 "N"
23401 "AAP" 110 0 262 "B"
23401 "AAP" 137978 480 249692 "Q"
23401 "AAP" 1049 15 1941 "N"
23401 "AAPB" 6813 0 10314 "Q"
23401 "AAPD" 900 0 901 "B"
23401 "AAPD" 25110 0 60014 "Q"
23401 "AAPD" 2600 0 17038 "N"
23401 "AAPJ" 400000 0 51192000 "O"
23401 "AAPL" 24070 0 39017 "B"
23401 "AAPL" 7113454 37617 14933484 "Q"
23401 "AAPL" 435800 8696 684506 "N"
23401 "AAPU" 9669 39 26320 "Q"
23401 "AAPU" 2 0 730 "N"
23401 "AAPX" 306 0 354 "Q"
23401 "AAPY" 62 0 70 "Q"
23401 "AASZF" 50 0 100 "O"
23401 "AAT" 38738 0 77039 "Q"
23401 "AAT" 123 0 177 "N"
23401 "AATC" 33733 0 66523 "O"
23401 "AAU" 6257 3850 29742 "Q"
23401 "AAU" 1763 0 5624 "N"
23401 "AAVMY" 4169 0 4723 "O"
23401 "AAVVF" 320 0 330 "O"
23401 "AAWH" 102754 0 129320 "O"
23401 "AAXJ" 43334 0 83881 "Q"
23401 "AAXJ" 119 0 702 "N"
23401 "AB" 56045 0 174319 "Q"
23401 "AB" 1009 0 1363 "N"
23401 "ABAT" 79441 2653 162790 "Q"
23401 "ABAT" 543 0 7789 "N"
23401 "ABBB" 400 0 400 "O"
23401 "ABBNY" 48799 0 202700 "O"
23401 "ABBRF" 72816 0 145216 "O"
23401 "ABBV" 2383 0 3715 "B"
23401 "ABBV" 515288 733 1198070 "Q"
23401 "ABBV" 6343 108 12774 "N"
23401 "ABCB" 162447 12 251023 "Q"
23401 "ABCB" 822 34 1844 "N"
23401 "ABCL" 221606 301 402003 "Q"
23401 "ABCL" 1595 0 4719 "N"
23401 "ABCS" 100 0 100 "N"
23401 "ABEO" 100 0 100 "B"
23401 "ABEO" 19281 0 58872 "Q"
23401 "ABEO" 2005 0 2470 "N"
23401 "ABEPF" 11725 0 11975 "O"
23401 "ABEQ" 100 0 483 "Q"
23401 "ABEV" 2317 0 3888 "B"
23401 "ABEV" 560951 0 1309770 "Q"
23401 "ABEV" 5484 0 13192 "N"
23401 "ABG" 39091 0 46199 "Q"
23401 "ABG" 206 0 329 "N"
23401 "ABGSF" 1 0 1 "O"
23401 "ABIO" 1600 0 2229 "B"
23401 "ABIO" 13749 310 31094 "Q"
23401 "ABIO" 171 21 2116 "N"
23401 "ABIT" 74352 0 121841 "O"
23401 "ABL" 3111 0 4433 "Q"
23401 "ABL" 26 0 111 "N"
23401 "ABLLW" 6247 0 25915 "Q"
end
format %tdnn/dd/CCYY Date
[/CODE]

* Example generated by -dataex-. For more info, type help dataex
clear
input str8(Date Symbol) str6(ShortExemptVolume ShortVolume) str7 TotalVolume str2 Market
"20240126" "A"       "0"    "281"   "405"    "A
"
"20240126" "AA"      "0"    "1577"  "3993"   "A
"
"20240126" "AAAU"    "0"    "700"   "700"    "A
"
"20240126" "AADI"    "0"    "331"   "331"    "A
"
"20240126" "AAL"     "0"    "28525" "146626" "A
"
"20240126" "AAMC"    "0"    "303"   "1726"   "A
"
"20240126" "AAN"     "0"    "38"    "157"    "A
"
"20240126" "AAOI"    "0"    "635"   "1601"   "A
"
"20240126" "AAON"    "0"    "117"   "117"    "A
"
"20240126" "AAP"     "0"    "0"     "404"    "A
"
"20240126" "AAPL"    "100"  "20225" "40249"  "A
"
"20240126" "AAPU"    "0"    "0"     "50"     "A
"
"20240126" "AAT"     "0"    "5"     "5"      "A
"
"20240126" "AAU"     "820"  "33492" "44060"  "A
"
"20240126" "AAXJ"    "0"    "464"   "664"    "A
"
"20240126" "AB"      "0"    "45"    "225"    "A
"
"20240126" "ABAT"    "0"    "205"   "626"    "A
"
"20240126" "ABBV"    "0"    "1011"  "2284"   "A
"
"20240126" "ABCB"    "0"    "630"   "635"    "A
"
"20240126" "ABCL"    "0"    "2595"  "3325"   "A
"
"20240126" "ABEV"    "0"    "6691"  "12291"  "A
"
"20240126" "ABG"     "0"    "160"   "201"    "A
"
"20240126" "ABIO"    "0"    "100"   "700"    "A
"
"20240126" "ABM"     "0"    "415"   "434"    "A
"
"20240126" "ABNB"    "3"    "11793" "15880"  "A
"
"20240126" "ABOS"    "0"    "616"   "816"    "A
"
"20240126" "ABR"     "0"    "1686"  "2561"   "A
"
"20240126" "ABR PRD" "0"    "0"     "122"    "A
"
"20240126" "ABR PRF" "0"    "200"   "714"    "A
"
"20240126" "ABSI"    "0"    "2175"  "2408"   "A
"
"20240126" "ABT"     "0"    "7638"  "8238"   "A
"
"20240126" "ABUS"    "0"    "1155"  "1155"   "A
"
"20240126" "ABVC"    "0"    "1305"  "1305"   "A
"
"20240126" "ACA"     "0"    "33"    "33"     "A
"
"20240126" "ACAD"    "1"    "710"   "983"    "A
"
"20240126" "ACB"     "0"    "329"   "3048"   "A
"
"20240126" "ACCD"    "0"    "429"   "456"    "A
"
"20240126" "ACCO"    "0"    "216"   "506"    "A
"
"20240126" "ACDC"    "400"  "1179"  "2399"   "A
"
"20240126" "ACEL"    "0"    "93"    "112"    "A
"
"20240126" "ACET"    "0"    "1993"  "2440"   "A
"
"20240126" "ACGL"    "0"    "839"   "1366"   "A
"
"20240126" "ACHC"    "0"    "177"   "177"    "A
"
"20240126" "ACHL"    "0"    "0"     "300"    "A
"
"20240126" "ACHR"    "0"    "1852"  "2986"   "A
"
"20240126" "ACHV"    "0"    "104"   "104"    "A
"
"20240126" "ACI"     "0"    "3710"  "5410"   "A
"
"20240126" "ACIC"    "0"    "102"   "313"    "A
"
"20240126" "ACIU"    "0"    "0"     "58"     "A
"
"20240126" "ACIW"    "0"    "593"   "649"    "A
"
"20240126" "ACLS"    "0"    "108"   "330"    "A
"
"20240126" "ACLX"    "0"    "296"   "302"    "A
"
"20240126" "ACM"     "0"    "93"    "240"    "A
"
"20240126" "ACMR"    "0"    "616"   "838"    "A
"
"20240126" "ACN"     "0"    "96"    "601"    "A
"
"20240126" "ACNB"    "0"    "111"   "120"    "A
"
"20240126" "ACON"    "3291" "4391"  "5562"   "A
"
"20240126" "ACR PRC" "0"    "500"   "895"    "A
"
"20240126" "ACRE"    "0"    "124"   "144"    "A
"
"20240126" "ACRS"    "0"    "887"   "1179"   "A
"
"20240126" "ACT"     "0"    "201"   "697"    "A
"
"20240126" "ACTG"    "0"    "500"   "800"    "A
"
"20240126" "ACU"     "14"   "2618"  "5621"   "A
"
"20240126" "ACVA"    "0"    "1765"  "3068"   "A
"
"20240126" "ACWI"    "0"    "1704"  "2710"   "A
"
"20240126" "ACWX"    "0"    "200"   "1552"   "A
"
"20240126" "ADAG"    "0"    "300"   "600"    "A
"
"20240126" "ADAP"    "0"    "0"     "975"    "A
"
"20240126" "ADBE"    "0"    "364"   "1259"   "A
"
"20240126" "ADC"     "0"    "9"     "174"    "A
"
"20240126" "ADCT"    "0"    "200"   "200"    "A
"
"20240126" "ADEA"    "0"    "354"   "481"    "A
"
"20240126" "ADES"    "0"    "0"     "200"    "A
"
"20240126" "ADI"     "6"    "892"   "1864"   "A
"
"20240126" "ADM"     "0"    "3226"  "11632"  "A
"
"20240126" "ADMA"    "0"    "344"   "1637"   "A
"
"20240126" "ADNT"    "0"    "514"   "1172"   "A
"
"20240126" "ADP"     "0"    "485"   "688"    "A
"
"20240126" "ADPT"    "0"    "1396"  "2196"   "A
"
"20240126" "ADRT"    "500"  "515"   "553"    "A
"
"20240126" "ADSK"    "0"    "142"   "1147"   "A
"
"20240126" "ADT"     "0"    "5362"  "8171"   "A
"
"20240126" "ADTH"    "100"  "101"   "131"    "A
"
"20240126" "ADTN"    "0"    "26"    "732"    "A
"
"20240126" "ADTX"    "0"    "0"     "35"     "A
"
"20240126" "ADUS"    "0"    "133"   "147"    "A
"
"20240126" "ADV"     "0"    "1325"  "1575"   "A
"
"20240126" "ADVM"    "0"    "500"   "500"    "A
"
"20240126" "ADX"     "0"    "100"   "108"    "A
"
"20240126" "AE"      "0"    "133"   "792"    "A
"
"20240126" "AEE"     "1"    "270"   "2022"   "A
"
"20240126" "AEF"     "0"    "1384"  "10637"  "A
"
"20240126" "AEFC"    "0"    "0"     "623"    "A
"
"20240126" "AEG"     "0"    "5239"  "7151"   "A
"
"20240126" "AEHR"    "0"    "2545"  "4515"   "A
"
"20240126" "AEIS"    "0"    "4"     "92"     "A
"
"20240126" "AEL"     "0"    "114"   "124"    "A
"
"20240126" "AEL PRA" "0"    "328"   "539"    "A
"
"20240126" "AEM"     "100"  "2646"  "5838"   "A
"
"20240126" "AEO"     "0"    "1357"  "2562"   "A
"
end

clear
input str8(Date Symbol) str5 ShortExemptVolume str7(ShortVolume TotalVolume) str2 Market
"20240126" "A" "0" "5323" "6088" "C
"
"20240126" "AA" "0" "11230" "15201" "C
"
"20240126" "AAA" "0" "700" "700" "C
"
"20240126" "AACG" "0" "100" "100" "C
"
"20240126" "AADI" "0" "1758" "1758" "C
"
"20240126" "AAGR" "0" "0" "978" "C
"
"20240126" "AAL" "102" "76888" "169647" "C
"
"20240126" "AAN" "0" "526" "927" "C
"
"20240126" "AAOI" "0" "587" "3503" "C
"
"20240126" "AAON" "0" "237" "238" "C
"
"20240126" "AAP" "0" "220" "555" "C
"
"20240126" "AAPD" "0" "104" "104" "C
"
"20240126" "AAPL" "12" "20835" "43979" "C
"
"20240126" "AAT" "0" "172" "426" "C
"
"20240126" "AAXJ" "0" "0" "10" "C
"
"20240126" "AB" "0" "170" "170" "C
"
"20240126" "ABAT" "0" "317" "611" "C
"
"20240126" "ABBV" "0" "2896" "6461" "C
"
"20240126" "ABCB" "0" "603" "719" "C
"
"20240126" "ABCL" "0" "2741" "3143" "C
"
"20240126" "ABEO" "0" "6" "6" "C
"
"20240126" "ABEV" "0" "30175" "69431" "C
"
"20240126" "ABG" "0" "211" "239" "C
"
"20240126" "ABL" "0" "400" "400" "C
"
"20240126" "ABM" "0" "287" "302" "C
"
"20240126" "ABNB" "100" "18012" "21004" "C
"
"20240126" "ABOS" "0" "118" "15551" "C
"
"20240126" "ABR" "0" "21171" "25157" "C
"
"20240126" "ABSI" "11" "623" "740" "C
"
"20240126" "ABT" "0" "2617" "3068" "C
"
"20240126" "ABUS" "0" "800" "3297" "C
"
"20240126" "ABVC" "0" "282" "282" "C
"
"20240126" "ACA" "0" "193" "194" "C
"
"20240126" "ACAD" "0" "300" "1053" "C
"
"20240126" "ACCD" "0" "1868" "2537" "C
"
"20240126" "ACCO" "0" "112" "912" "C
"
"20240126" "ACDC" "0" "1511" "2652" "C
"
"20240126" "ACEL" "0" "112" "112" "C
"
"20240126" "ACET" "0" "4106" "4789" "C
"
"20240126" "ACGL" "0" "375" "2209" "C
"
"20240126" "ACHC" "0" "200" "200" "C
"
"20240126" "ACHL" "0" "0" "300" "C
"
"20240126" "ACHR" "0" "6236" "9096" "C
"
"20240126" "ACHV" "0" "5810" "6909" "C
"
"20240126" "ACI" "0" "3411" "8220" "C
"
"20240126" "ACIC" "0" "42" "119" "C
"
"20240126" "ACIO" "0" "674" "674" "C
"
"20240126" "ACIW" "0" "265" "465" "C
"
"20240126" "ACLS" "0" "596" "638" "C
"
"20240126" "ACLX" "0" "804" "898" "C
"
"20240126" "ACM" "0" "417" "763" "C
"
"20240126" "ACMR" "0" "647" "865" "C
"
"20240126" "ACN" "45" "618" "1458" "C
"
"20240126" "ACNB" "0" "125" "179" "C
"
"20240126" "ACON" "3598" "7344" "9273" "C
"
"20240126" "ACR" "0" "0" "300" "C
"
"20240126" "ACRE" "0" "80" "80" "C
"
"20240126" "ACRS" "0" "5139" "8343" "C
"
"20240126" "ACT" "0" "0" "29" "C
"
"20240126" "ACTV" "0" "8" "9" "C
"
"20240126" "ACU" "0" "410" "466" "C
"
"20240126" "ACVA" "0" "3179" "7236" "C
"
"20240126" "ACVF" "0" "552" "552" "C
"
"20240126" "ACWI" "0" "11061" "12767" "C
"
"20240126" "ACWX" "0" "825" "4325" "C
"
"20240126" "ADAG" "0" "0" "220" "C
"
"20240126" "ADAP" "0" "0" "2467" "C
"
"20240126" "ADBE" "0" "1813" "3959" "C
"
"20240126" "ADC" "0" "355" "416" "C
"
"20240126" "ADCT" "0" "1054" "1258" "C
"
"20240126" "ADEA" "0" "621" "799" "C
"
"20240126" "ADES" "0" "0" "3061" "C
"
"20240126" "ADI" "17" "1580" "3175" "C
"
"20240126" "ADM" "0" "4136" "8325" "C
"
"20240126" "ADMA" "0" "5229" "6061" "C
"
"20240126" "ADN" "0" "1688" "4413" "C
"
"20240126" "ADNT" "0" "10154" "18469" "C
"
"20240126" "ADP" "1" "1055" "1356" "C
"
"20240126" "ADPT" "0" "4483" "8028" "C
"
"20240126" "ADSE" "0" "1597" "3997" "C
"
"20240126" "ADSK" "0" "843" "1043" "C
"
"20240126" "ADT" "0" "5519" "28380" "C
"
"20240126" "ADTH" "0" "0" "613" "C
"
"20240126" "ADTN" "0" "35" "64" "C
"
"20240126" "ADUS" "0" "43" "49" "C
"
"20240126" "ADV" "0" "12397" "14522" "C
"
"20240126" "ADVM" "0" "500" "774" "C
"
"20240126" "ADX" "0" "472" "972" "C
"
"20240126" "ADXN" "0" "71" "74" "C
"
"20240126" "AEE" "11" "1004" "1644" "C
"
"20240126" "AEF" "0" "0" "100" "C
"
"20240126" "AEG" "0" "17623" "28781" "C
"
"20240126" "AEHR" "0" "146" "163" "C
"
"20240126" "AEIS" "0" "78" "88" "C
"
"20240126" "AEL" "0" "7" "49" "C
"
"20240126" "AEM" "2511" "5450" "10229" "C
"
"20240126" "AEO" "0" "1693" "3853" "C
"
"20240126" "AEP" "2" "994" "2067" "C
"
"20240126" "AER" "0" "7138" "9833" "C
"
"20240126" "AERT" "0" "100" "100" "C
"
end
[/CODE]

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 Date str7 Symbol str5 ShortExemptVolume str6 ShortVolume str7 TotalVolume str2 Market
"20240126" "A"     "0"    "3466"  "9084"    "M
"
"20240126" "AA"    "0"    "931"   "1131"    "M
"
"20240126" "AADI"  "0"    "7"     "7"       "M
"
"20240126" "AAL"   "0"    "17855" "146794"  "M
"
"20240126" "AAN"   "0"    "254"   "304"     "M
"
"20240126" "AAOI"  "0"    "3874"  "9526"    "M
"
"20240126" "AAON"  "0"    "1616"  "2856"    "M
"
"20240126" "AAP"   "0"    "5572"  "7020"    "M
"
"20240126" "AAPL"  "0"    "46134" "697739"  "M
"
"20240126" "AAT"   "0"    "998"   "1493"    "M
"
"20240126" "AB"    "0"    "2699"  "4170"    "M
"
"20240126" "ABAT"  "0"    "829"   "1280"    "M
"
"20240126" "ABBV"  "60"   "10762" "1003227" "M
"
"20240126" "ABCB"  "0"    "3900"  "7148"    "M
"
"20240126" "ABCL"  "0"    "149"   "1266"    "M
"
"20240126" "ABEO"  "0"    "200"   "400"     "M
"
"20240126" "ABEV"  "0"    "100"   "2600"    "M
"
"20240126" "ABG"   "0"    "912"   "1511"    "M
"
"20240126" "ABIO"  "0"    "0"     "100"     "M
"
"20240126" "ABL"   "0"    "52"    "52"      "M
"
"20240126" "ABM"   "0"    "1195"  "1426"    "M
"
"20240126" "ABNB"  "0"    "39450" "45614"   "M
"
"20240126" "ABOS"  "0"    "196"   "196"     "M
"
"20240126" "ABR"   "0"    "448"   "548"     "M
"
"20240126" "ABSI"  "0"    "545"   "1045"    "M
"
"20240126" "ABT"   "0"    "9136"  "29615"   "M
"
"20240126" "ABUS"  "0"    "8450"  "8450"    "M
"
"20240126" "ABVC"  "0"    "100"   "100"     "M
"
"20240126" "ABVX"  "0"    "698"   "998"     "M
"
"20240126" "ACA"   "0"    "598"   "1059"    "M
"
"20240126" "ACAD"  "0"    "1589"  "1871"    "M
"
"20240126" "ACB"   "1300" "6000"  "13400"   "M
"
"20240126" "ACCD"  "0"    "853"   "1110"    "M
"
"20240126" "ACCO"  "0"    "85"    "93"      "M
"
"20240126" "ACDC"  "0"    "228"   "751"     "M
"
"20240126" "ACEL"  "0"    "126"   "215"     "M
"
"20240126" "ACET"  "0"    "392"   "392"     "M
"
"20240126" "ACGL"  "0"    "4607"  "7170"    "M
"
"20240126" "ACHC"  "0"    "918"   "1333"    "M
"
"20240126" "ACHR"  "0"    "443"   "1137"    "M
"
"20240126" "ACI"   "0"    "377"   "8786"    "M
"
"20240126" "ACIC"  "0"    "684"   "1126"    "M
"
"20240126" "ACIW"  "0"    "414"   "543"     "M
"
"20240126" "ACLS"  "0"    "3649"  "5094"    "M
"
"20240126" "ACLX"  "0"    "6253"  "7939"    "M
"
"20240126" "ACM"   "0"    "929"   "2615"    "M
"
"20240126" "ACMR"  "0"    "2044"  "2896"    "M
"
"20240126" "ACN"   "108"  "14349" "17941"   "M
"
"20240126" "ACNB"  "0"    "18"    "18"      "M
"
"20240126" "ACON"  "0"    "204"   "549"     "M
"
"20240126" "ACRE"  "0"    "107"   "107"     "M
"
"20240126" "ACRS"  "0"    "1520"  "2674"    "M
"
"20240126" "ACRV"  "0"    "0"     "49"      "M
"
"20240126" "ACT"   "0"    "1332"  "2135"    "M
"
"20240126" "ACU"   "0"    "286"   "390"     "M
"
"20240126" "ACVA"  "0"    "189"   "781"     "M
"
"20240126" "ACWI"  "0"    "6768"  "2011750" "M
"
"20240126" "ACXP"  "0"    "594"   "594"     "M
"
"20240126" "ADAG"  "0"    "0"     "259"     "M
"
"20240126" "ADAP"  "0"    "10310" "11200"   "M
"
"20240126" "ADBE"  "0"    "5113"  "23809"   "M
"
"20240126" "ADC"   "0"    "161"   "1109"    "M
"
"20240126" "ADCT"  "0"    "3920"  "5626"    "M

"20240126" "AFYA"  "0"    "0"     "22"      "M
"
end

* Example generated by -dataex-. For more info, type help dataex
clear
input str8(Date Symbol) str6 ShortExemptVolume str7 ShortVolume str8 TotalVolume str2 Market
"20240126" "A"       "2"     "226418" "408482"  "N
"
"20240126" "AA"      "5080"  "328871" "835567"  "N
"
"20240126" "AAA"     "0"     "100"    "100"     "N
"
"20240126" "AAAU"    "0"     "19510"  "20010"   "N
"
"20240126" "AACT"    "0"     "0"      "1200"    "N
"
"20240126" "AACT WS" "0"     "0"      "29499"   "N
"
"20240126" "AADI"    "0"     "1262"   "2264"    "N
"
"20240126" "AAGR"    "0"     "0"      "900"     "N
"
"20240126" "AAL"     "600"   "482368" "858528"  "N
"
"20240126" "AAN"     "1"     "29081"  "84888"   "N
"
"20240126" "AAOI"    "0"     "8494"   "26053"   "N
"
"20240126" "AAON"    "0"     "1277"   "1389"    "N
"
"20240126" "AAP"     "173"   "119574" "236126"  "N
"
"20240126" "AAPB"    "0"     "940"    "1029"    "N
"
"20240126" "AAPD"    "0"     "7197"   "7795"    "N
"
"20240126" "AAPL"    "1331"  "550350" "976669"  "N
"
"20240126" "AAPU"    "0"     "30"     "2730"    "N
"
"20240126" "AAT"     "61"    "42117"  "94935"   "N
"
"20240126" "AAU"     "0"     "225"    "225"     "N
"
"20240126" "AAXJ"    "0"     "6504"   "11674"   "N
"
"20240126" "AB"      "0"     "14713"  "26754"   "N
"
"20240126" "ABAT"    "0"     "1939"   "2653"    "N
"
"20240126" "ABBV"    "3063"  "640583" "1147251" "N
"
"20240126" "ABCB"    "0"     "4335"   "6200"    "N
"
"20240126" "ABCL"    "0"     "36229"  "48807"   "N
"
"20240126" "ABEO"    "0"     "218"    "751"     "N
"
"20240126" "ABEV"    "31"    "267626" "582768"  "N
"
"20240126" "ABG"     "14"    "26852"  "39341"   "N
"
"20240126" "ABLLL"   "0"     "0"      "200"     "N
"
"20240126" "ABM"     "188"   "54926"  "147857"  "N
"
"20240126" "ABNB"    "460"   "120998" "160331"  "N
"
"20240126" "ABOS"    "0"     "11374"  "19056"   "N
"
"20240126" "ABR"     "2896"  "321765" "598943"  "N
"
"20240126" "ABR PRD" "0"     "154"    "1676"    "N
"
"20240126" "ABR PRE" "0"     "499"    "1409"    "N
"
"20240126" "ABR PRF" "0"     "374"    "12304"   "N
"
"20240126" "ABSI"    "0"     "26258"  "30983"   "N
"
"20240126" "ABT"     "1258"  "993038" "1476874" "N
"
"20240126" "ABUS"    "0"     "43293"  "43953"   "N
"
"20240126" "ABVC"    "0"     "2365"   "3861"    "N
"
"20240126" "ABVX"    "0"     "311"    "1279"    "N
"
"20240126" "AC"      "0"     "282"    "1171"    "N
"
"20240126" "ACA"     "28"    "23362"  "41799"   "N
"
"20240126" "ACAD"    "82"    "8715"   "14924"   "N
"
"20240126" "ACB"     "600"   "8423"   "15049"   "N
"
"20240126" "ACCD"    "0"     "5129"   "7756"    "N
"
"20240126" "ACCO"    "115"   "38817"  "94250"   "N
"
"20240126" "ACDC"    "0"     "9474"   "16446"   "N
"
"20240126" "ACEL"    "0"     "46229"  "75929"   "N
"
"20240126" "ACET"    "0"     "9347"   "17784"   "N
"
"20240126" "ACGL"    "0"     "8535"   "15978"   "N
"
"20240126" "ACGLN"   "0"     "0"      "142"     "N
"
"20240126" "ACGLO"   "0"     "54"     "62"      "N
"
"20240126" "ACHC"    "0"     "2413"   "3007"    "N
"
"20240126" "ACHL"    "0"     "0"      "418"     "N
"
"20240126" "ACHR"    "1346"  "585679" "746923"  "N
"
"20240126" "ACHR WS" "0"     "831"    "1338"    "N
"
"20240126" "ACHV"    "0"     "100"    "200"     "N
"
"20240126" "ACI"     "3578"  "223903" "453716"  "N
"
"20240126" "ACIC"    "0"     "2951"   "3884"    "N
"
"20240126" "ACIO"    "0"     "100"    "176"     "N
"
"20240126" "ACIU"    "0"     "3955"   "4427"    "N
"
"20240126" "ACIW"    "0"     "6384"   "10666"   "N
"
"20240126" "ACLS"    "146"   "4965"   "6309"    "N
"
"20240126" "ACLX"    "0"     "7033"   "8545"    "N
"
"20240126" "ACM"     "41"    "38579"  "175482"  "N
"
"20240126" "ACMR"    "0"     "3379"   "8416"    "N
"
"20240126" "ACN"     "492"   "282543" "460164"  "N
"
"20240126" "ACNB"    "0"     "27"     "27"      "N
"
"20240126" "ACON"    "81772" "120659" "179532"  "N
"
"20240126" "ACP"     "4445"  "15394"  "25696"   "N
"
"20240126" "ACP PRA" "0"     "0"      "186"     "N
"
"20240126" "ACR"     "1"     "937"    "12222"   "N
"
"20240126" "ACR PRC" "0"     "260"    "3062"    "N
"
"20240126" "ACR PRD" "0"     "0"      "211"     "N
"
"20240126" "ACRE"    "923"   "64269"  "102470"  "N
"
"20240126" "ACRS"    "0"     "13698"  "25052"   "N
"
"20240126" "ACRV"    "0"     "74"     "174"     "N
"
"20240126" "ACT"     "47"    "5604"   "16494"   "N
"
"20240126" "ACTG"    "98"    "2774"   "3592"    "N
"
"20240126" "ACTV"    "0"     "300"    "301"     "N
"
"20240126" "ACV"     "0"     "2218"   "6618"    "N
"
"20240126" "ACVA"    "0"     "9378"   "17653"   "N
"
"20240126" "ACWI"    "700"   "53389"  "110165"  "N
"
"20240126" "ACWV"    "0"     "1550"   "2000"    "N
"
"20240126" "ACWX"    "0"     "6426"   "13546"   "N
"
"20240126" "ACXP"    "0"     "231"    "231"     "N
"
"20240126" "ADAG"    "0"     "0"      "722"     "N
"
"20240126" "ADAP"    "0"     "100"    "3108"    "N
"
"20240126" "ADBE"    "8"     "18992"  "33772"   "N
"
"20240126" "ADC"     "34"    "48903"  "123897"  "N
"
"20240126" "ADC PRA" "0"     "17"     "6158"    "N
"
"20240126" "ADCT"    "749"   "59724"  "162041"  "N
"
"20240126" "ADEA"    "0"     "5587"   "7124"    "N
"
"20240126" "ADES"    "0"     "0"      "7"       "N
"
"20240126" "ADI"     "245"   "11031"  "27062"   "N
"
"20240126" "ADM"     "3620"  "500953" "2705825" "N
"
"20240126" "ADMA"    "220"   "20302"  "36625"   "N
"
"20240126" "ADN"     "0"     "1690"   "3192"    "N
"
"20240126" "ADNT"    "2466"  "122359" "283838"  "N
"
end

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 Date str6 Symbol str8(ShortExemptVolume ShortVolume TotalVolume) str2 Market
"20240126" "A"     "0"       "28005"   "34446"   "P
"
"20240126" "AA"    "400"     "72653"   "186982"  "P
"
"20240126" "AAA"   "0"       "1954"    "2153"    "P
"
"20240126" "AAAU"  "0"       "202435"  "211614"  "P
"
"20240126" "AACIW" "0"       "2568"    "2968"    "P
"
"20240126" "AADI"  "0"       "5193"    "5897"    "P
"
"20240126" "AAGR"  "25"      "425"     "2730"    "P
"
"20240126" "AAGRW" "0"       "4447"    "4447"    "P
"
"20240126" "AAL"   "700"     "1202837" "2631350" "P
"
"20240126" "AAMC"  "0"       "305"     "1632"    "P
"
"20240126" "AAME"  "0"       "16"      "202"     "P
"
"20240126" "AAN"   "0"       "1552"    "4547"    "P
"
"20240126" "AAOI"  "170"     "67570"   "154946"  "P
"
"20240126" "AAON"  "100"     "5949"    "8874"    "P
"
"20240126" "AAP"   "0"       "13449"   "26671"   "P
"
"20240126" "AAPB"  "0"       "574"     "890"     "P
"
"20240126" "AAPD"  "0"       "5878"    "8821"    "P
"
"20240126" "AAPL"  "4631"    "1141342" "2290608" "P
"
"20240126" "AAPU"  "0"       "1022"    "2538"    "P
"
"20240126" "AAPX"  "0"       "10"      "31"      "P
"
"20240126" "AAPY"  "0"       "20"      "52"      "P
"
"20240126" "AAT"   "101"     "3326"    "7461"    "P
"
"20240126" "AAU"   "0"       "60"      "268"     "P
"
"20240126" "AAXJ"  "0"       "31045"   "45499"   "P
"
"20240126" "AB"    "0"       "3406"    "4181"    "P
"
"20240126" "ABAT"  "0"       "2625"    "5235"    "P
"
"20240126" "ABBV"  "129"     "90467"   "137494"  "P
"
"20240126" "ABCB"  "105"     "10966"   "15131"   "P
"
"20240126" "ABCL"  "0"       "131535"  "169117"  "P
"
"20240126" "ABEO"  "0"       "193"     "1247"    "P
"
"20240126" "ABEQ"  "0"       "19"      "31"      "P
"
"20240126" "ABEV"  "0"       "50489"   "86427"   "P
"
"20240126" "ABG"   "0"       "2178"    "2894"    "P
"
"20240126" "ABIO"  "0"       "400"     "1384"    "P
"
"20240126" "ABL"   "0"       "260"     "326"     "P
"
"20240126" "ABLLW" "0"       "436"     "10071"   "P
"
"20240126" "ABLV"  "0"       "0"       "1"       "P
"
"20240126" "ABM"   "12"      "5607"    "12663"   "P
"
"20240126" "ABNB"  "339"     "504031"  "716203"  "P
"
"20240126" "ABOS"  "0"       "27710"   "38876"   "P
"
"20240126" "ABR"   "0"       "76826"   "108376"  "P
"
"20240126" "ABR-D" "0"       "0"       "202"     "P
"
"20240126" "ABR-E" "0"       "150"     "306"     "P
"
"20240126" "ABR-F" "0"       "67"      "6095"    "P
"
"20240126" "ABSI"  "0"       "40897"   "52499"   "P
"
"20240126" "ABT"   "100"     "88525"   "143010"  "P
"
"20240126" "ABTS"  "0"       "0"       "110"     "P
"
"20240126" "ABUS"  "0"       "17156"   "21833"   "P
"
"20240126" "ABVC"  "0"       "5695"    "10782"   "P
"
"20240126" "ABVX"  "0"       "3540"    "5145"    "P
"
"20240126" "AC"    "0"       "2"       "104"     "P
"
"20240126" "ACA"   "0"       "1242"    "3527"    "P
"
"20240126" "ACABW" "0"       "0"       "1"       "P
"
"20240126" "ACAD"  "200"     "31278"   "50961"   "P
"
"20240126" "ACAH"  "0"       "0"       "1"       "P
"
"20240126" "ACB"   "12978"   "104483"  "166758"  "P
"
"20240126" "ACCD"  "23"      "14656"   "32172"   "P
"
"20240126" "ACCO"  "148"     "3756"    "5813"    "P
"
"20240126" "ACDC"  "100"     "43289"   "64212"   "P
"
"20240126" "ACEL"  "0"       "3004"    "3513"    "P
"
"20240126" "ACES"  "50"      "40946"   "59318"   "P
"
"20240126" "ACET"  "0"       "22397"   "51156"   "P
"
"20240126" "ACGL"  "102"     "31665"   "69008"   "P
"
"20240126" "ACGLN" "0"       "0"       "321"     "P
"
"20240126" "ACGLO" "0"       "0"       "87"      "P
"
"20240126" "ACHC"  "0"       "13065"   "14729"   "P
"
"20240126" "ACHL"  "500"     "600"     "4614"    "P
"
"20240126" "ACHR"  "99"      "63669"   "85091"   "P
"
"20240126" "ACHR+" "0"       "20"      "120"     "P
"
"20240126" "ACHV"  "0"       "2295"    "3698"    "P
"
"20240126" "ACI"   "445"     "42744"   "79254"   "P
"
"20240126" "ACIC"  "0"       "5994"    "6918"    "P
"
"20240126" "ACIO"  "0"       "238"     "238"     "P
"
"20240126" "ACIU"  "0"       "19136"   "29733"   "P
"
"20240126" "ACIW"  "100"     "13417"   "31063"   "P
"
"20240126" "ACLS"  "5"       "28910"   "36035"   "P
"
"20240126" "ACLX"  "97"      "21320"   "36176"   "P
"
"20240126" "ACM"   "100"     "2511"    "6172"    "P
"
"20240126" "ACMR"  "288"     "49496"   "87355"   "P
"
"20240126" "ACN"   "56"      "39893"   "56253"   "P
"
"20240126" "ACNB"  "0"       "130"     "132"     "P
"
"20240126" "ACNT"  "0"       "326"     "326"     "P
"
"20240126" "ACON"  "1156188" "1650149" "2605277" "P
"
"20240126" "ACONW" "0"       "97"      "11389"   "P
"
"20240126" "ACOR"  "0"       "0"       "100"     "P
"
"20240126" "ACP"   "0"       "1503"    "1833"    "P
"
"20240126" "ACR"   "0"       "2103"    "2212"    "P
"
"20240126" "ACR-C" "0"       "0"       "1000"    "P
"
"20240126" "ACRE"  "134"     "4764"    "5939"    "P
"
"20240126" "ACRS"  "0"       "22575"   "38391"   "P
"
"20240126" "ACRV"  "0"       "16858"   "21946"   "P
"
"20240126" "ACSI"  "0"       "3"       "5"       "P

"
end

[Andrew Musau]

In datasets 2 through 6, run

use filename
gen date= daily(Date, "YMD"), before(Date)
drop Date
rename date Date
destring ShortExemptVolume ShortVolume TotalVolume, replace
save filename

where you replace "filename" with the name of the dataset. Then append with dataset 1 as is.

[Bashar Fin]

Dear Andrew.
Thank you very much. Regarding this process, as I will be doing it frequently, could I save time by importing and saving these files once, rather than importing them into Stata individually each time and then running your codes? Thank you

[Andrew Musau]

No, you cannot append datasets where the same variable is a string in one dataset and numeric in the other. However, given that you know the filenames, you could incorporate the code in #4 into the loop. The following is not tested.

clear
tempfile holding
use file1, clear
save `holding'
foreach filename in fileX fileZ myfile{
    use `filename', clear
    gen date= daily(Date, "YMD"), before(Date)
    drop Date
    rename date Date
    destring ShortExemptVolume ShortVolume TotalVolume, replace
    save "`filename'", replace
    append using `holding'
    save `holding', replace
}
use `holding', clear

[Bashar Fin]

Dear Andrew,

I truly appreciate your help. Yes, it works. Please, I have just one question, excuse me: for those same codes and procedures, can I write the code like in #6 but instead of using Stata datasets, use Excel files? Because I need to skip the step of importing each Excel file into Stata every day to save it as .dta and go directly to the codes in #6.Assume all the excel files in the same folder in my desktop.

Many thanks.

[Andrew Musau]

Certainly.

clear
tempfile holding
import excel using file1.xls, clear firstrow
save `holding'
foreach filename in fileX fileZ myfile{
    import excel using `filename'.xls, clear firstrow
    gen date= daily(Date, "YMD"), before(Date)
    drop Date
    rename date Date
    destring ShortExemptVolume ShortVolume TotalVolume, replace
    save "`filename'", replace
    append using `holding'
    save `holding', replace
}
use `holding', clear

import excel has an -allstring- option which can allow you to append all datasets as they are, and then you can clean the appended dataset.

clear
tempfile holding
save `holding', emptyok
local files: dir "..." files "*.xlsx" // REPLACE "..." BY PATHNAME WHERE YOUR FILES LIE
foreach f of local files{
    import excel using "path/`f'", allstring firstrow clear
    append using `holding', replace
}
use `holding', clear

[Bashar Fin]

Dear Andrew Musau , thank you so much for your help.