Appending data from multiple sources

[Giles German]

Dear All,

I am trying to do the following: -

1. Take the source data set.
2. Drop the web into two different macros with the ticker name,
3. Collect the close prices from each of the ticker names for quandl (subscription service you can use yahoo if you want to check this)
4. Append these into a single file
5. Collect more from google
6. Append these into the temp same file
7. Format the file such as change the date (I want to report monthly)
8. 1:1 Merge this into the master data set.
9. raise a panel data set with lagged returns (subject of another post)
10. Data ready for regression.

Problems I am having.

I can't get the macro to fly put Japanese data through the quandl data set.
I can't format the fully appended file correctly and in the correct date format.
I can't manage to merge it all together as I have duplicates.

generally a bit of a mess.

Your help is greatly appreciated.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float date str7 ticker str2 reporttype str5 mic str10 reportdate long(compnumber reportid) str3 currency str60 shortname str3 countrycode str13 region str3(exchange country) str10 fye str1 sector long sectnum str6 web
395 "" "A" "1.2.1" "1992-12-31"  84609 1547696128 "DEM" "Pinguin Haustechnik AG (Germa"  "DEU" "Europe"       "NL"  "DEU" "2000-12-31" "1" 2 "google"
407 "" "A" "1.2.1" "1993-12-31"  84609 1564566784 "DEM" "Pinguin Haustechnik AG (Germa"  "DEU" "Europe"       "NL"  "DEU" "2000-12-31" "1" 2 "google"
419 "" "A" "1.2.1" "1994-12-31"  84609 1581437440 "DEM" "Pinguin Haustechnik AG (Germa"  "DEU" "Europe"       "NL"  "DEU" "2000-12-31" "1" 2 "google"
455 "" "A" "1.2.1" "1997-12-31"  84609 1632049665 "DEM" "Pinguin Haustechnik AG (Germa"  "DEU" "Europe"       "NL"  "DEU" "2000-12-31" "1" 2 "google"
458 "" "A" "1.8.1" "1998-03-31"  80597 1648849921 "INR" "Bajaj Holdings & Investment"    "IND" "Asia Pacific" "NL"  "IN"  "2015-03-31" "1" 2 "google"
467 "" "A" "1.2.1" "1998-12-31"  84609 1648920321 "DEM" "Pinguin Haustechnik AG (Germa"  "DEU" "Europe"       "NL"  "DEU" "2000-12-31" "1" 2 "google"
470 "" "A" "1.4.1" "1999-03-31"  90674 1665720577 "INR" "Vardhman Textiles"              "IND" "Asia Pacific" "NL"  "IN"  "2015-03-31" "1" 2 "google"
479 "" "A" "1.2.1" "1999-12-31"  84609 1665790976 "DEM" "Pinguin Haustechnik AG (Germa"  "DEU" "Europe"       "NL"  "DEU" "2000-12-31" "1" 2 "google"
482 "" "A" "1.4.1" "2000-03-31"  90674 1682591489 "INR" "Vardhman Textiles"              "IND" "Asia Pacific" "NL"  "IN"  "2015-03-31" "1" 2 "google"
491 "" "A" "1.2.1" "2000-12-31"  84609 1682661888 "DEM" "Pinguin Haustechnik AG (Germa"  "DEU" "Europe"       "NL"  "DEU" "2000-12-31" "1" 2 "google"
494 "" "A" "1.4.1" "2001-03-31"  90102 1699462145 "INR" "Himatsingka Seide"              "IND" "Asia Pacific" "NL"  "IN"  "2015-03-31" "1" 2 "google"
503 "" "A" "1.6.3" "2001-12-31"  97412 1699532544 "EUR" "Bulgari SpA"                    "ITA" "Europe"       "NL"  "ITA" "2010-12-31" "1" 2 "google"
506 "" "A" "1.4.1" "2002-03-31"  90674 1716332800 "INR" "Vardhman Textiles"              "IND" "Asia Pacific" "NL"  "IN"  "2015-03-31" "1" 2 "google"
515 "" "A" "1.6.3" "2002-12-31"  97412 1716403200 "EUR" "Bulgari SpA"                    "ITA" "Europe"       "NL"  "ITA" "2010-12-31" "1" 2 "google"
518 "" "A" "1.2.1" "2003-03-29"  83641 1733202944 "GBP" "Wiseman (Robert) Dairies PLC"   "GBR" "Europe"       "NL"  "GBR" "2015-12-31" "1" 2 "google"
527 "" "A" "1.6.3" "2003-12-31"  70196 1733273856 "EUR" "WMF AG"                         "DEU" "Europe"       "NL"  "DEU" "2013-12-31" "1" 2 "google"
530 "" "A" "1.8.1" "2004-03-31"  80597 1750074368 "INR" "Bajaj Holdings & Investment"    "IND" "Asia Pacific" "NL"  "IN"  "2015-03-31" "1" 2 "google"
531 "" "A" "1.2.1" "2004-04-03"  83641 1750075136 "GBP" "Wiseman (Robert) Dairies PLC"   "GBR" "Europe"       "NL"  "GBR" "2015-12-31" "1" 2 "google"
539 "" "A" "1.6.3" "2004-12-31"  97412 1750144768 "EUR" "Bulgari SpA"                    "ITA" "Europe"       "NL"  "ITA" "2010-12-31" "1" 2 "google"
542 "" "A" "1.8.1" "2005-03-31"  80597 1766945024 "INR" "Bajaj Holdings & Investment"    "IND" "Asia Pacific" "NL"  "IN"  "2015-03-31" "1" 2 "google"
543 "" "A" "1.2.1" "2005-04-02"  83641 1766945536 "GBP" "Wiseman (Robert) Dairies PLC"   "GBR" "Europe"       "NL"  "GBR" "2015-12-31" "1" 2 "google"
545 "" "A" "1.1.1" "2005-06-30"  88449 1766968320 "EUR" "KWS SAAT AG (Germany)"          "DEU" "Europe"       "NL"  "DEU" "2017-06-30" "1" 2 "google"
547 "" "A" "1.6.1" "2005-08-31"  84695 1766984192 "EUR" "Beneteau S.A."                  "FRA" "Europe"       "NL"  "FRA" "2016-08-31" "1" 2 "google"
550 "" "A" "1.4.1" "2005-11-30"  85521 1767007488 "JPY" "Japan Wool Textile Co., Ltd."   "JPN" "Asia Pacific" "TOK" "JPN" "2016-11-30" "1" 2 "quandl"
551 "" "A" "1.2.2" "2005-12-31"  55033 1767015424 "GBP" "Nichols Plc (United Kingdom)"   "GBR" "Europe"       "NL"  "GBR" "2016-12-31" "1" 2 "google"
553 "" "A" "1.2.1" "2006-02-28" 114174 1783807744 "JPY" "S Foods Inc"                    "JPN" "Asia Pacific" "TOK" "JPN" "2017-02-28" "1" 2 "quandl"
554 "" "A" "1.8.2" "2006-03-31" 100385 1783815680 "JPY" "Imasen Electric Industrial Co." "JPN" "Asia Pacific" "TOK" "JPN" "2017-03-31" "1" 2 "quandl"
555 "" "A" "1.2.1" "2006-04-01"  83641 1783815936 "GBP" "Wiseman (Robert) Dairies PLC"   "GBR" "Europe"       "NL"  "GBR" "2015-12-31" "1" 2 "google"
559 "" "A" "1.6.1" "2006-08-31"  84695 1783854848 "EUR" "Beneteau S.A."                  "FRA" "Europe"       "NL"  "FRA" "2016-08-31" "1" 2 "google"
560 "" "A" "1.7.1" "2006-09-20"  97127 1783859968 "JPY" "Noevir Holdings Co., Ltd."      "JPN" "Asia Pacific" "TOK" "JPN" "2016-09-30" "1" 2 "quandl"
562 "" "A" "1.4.1" "2006-11-30"  85521 1783878144 "JPY" "Japan Wool Textile Co., Ltd."   "JPN" "Asia Pacific" "TOK" "JPN" "2016-11-30" "1" 2 "quandl"
563 "" "A" "1.8.2" "2006-12-31" 101061 1783886080 "EUR" "Grammer Ag (Germany)"           "DEU" "Europe"       "NL"  "DEU" "2016-12-31" "1" 2 "google"
565 "" "A" "1.2.1" "2007-02-28" 114174 1800678400 "JPY" "S Foods Inc"                    "JPN" "Asia Pacific" "TOK" "JPN" "2017-02-28" "1" 2 "quandl"
566 "" "A" "1.2.2" "2007-03-31"  71276 1800686336 "EUR" "Remy Cointreau SA (France)"     "FRA" "Europe"       "NL"  "FRA" "2017-03-31" "1" 2 "google"
567 "" "A" "1.2.2" "2007-04-02"  91919 1800686848 "GBP" "Majestic Wine Plc (United Ki"   "GBR" "Europe"       "NL"  "GBR" "2017-04-03" "1" 2 "google"
569 "" "A" "1.1.1" "2007-06-30"  88449 1800709632 "EUR" "KWS SAAT AG (Germany)"          "DEU" "Europe"       "NL"  "DEU" "2017-06-30" "1" 2 "google"
571 "" "A" "1.6.1" "2007-08-31"  84695 1800725504 "EUR" "Beneteau S.A."                  "FRA" "Europe"       "NL"  "FRA" "2016-08-31" "1" 2 "google"
572 "" "A" "1.7.1" "2007-09-20"  97127 1800730624 "JPY" "Noevir Holdings Co., Ltd."      "JPN" "Asia Pacific" "TOK" "JPN" "2016-09-30" "1" 2 "quandl"
574 "" "A" "1.4.1" "2007-11-30"  85521 1800748800 "JPY" "Japan Wool Textile Co., Ltd."   "JPN" "Asia Pacific" "TOK" "JPN" "2016-11-30" "1" 2 "quandl"
575 "" "A" "1.2.1" "2007-12-31"  70268 1800756736 "EUR" "Campofrio Food Group SA"        "ESP" "Europe"       "NL"  "ESP" "2016-12-31" "1" 2 "google"
577 "" "A" "1.2.1" "2008-02-29" 114174 1817549312 "JPY" "S Foods Inc"                    "JPN" "Asia Pacific" "TOK" "JPN" "2017-02-28" "1" 2 "quandl"
578 "" "A" "1.2.1" "2008-03-31"  66695 1817557248 "JPY" "Fujiya Co., Ltd. (Japan)"       "JPN" "Asia Pacific" "TOK" "JPN" "2016-12-31" "1" 2 "quandl"
581 "" "A" "1.1.1" "2008-06-30"  88449 1817580544 "EUR" "KWS SAAT AG (Germany)"          "DEU" "Europe"       "NL"  "DEU" "2017-06-30" "1" 2 "google"
583 "" "A" "1.6.1" "2008-08-31"  84695 1817596416 "EUR" "Beneteau S.A."                  "FRA" "Europe"       "NL"  "FRA" "2016-08-31" "1" 2 "google"
584 "" "A" "1.7.1" "2008-09-20"  97127 1817601536 "JPY" "Noevir Holdings Co., Ltd."      "JPN" "Asia Pacific" "TOK" "JPN" "2016-09-30" "1" 2 "quandl"
586 "" "A" "1.4.1" "2008-11-30"  85521 1817619712 "JPY" "Japan Wool Textile Co., Ltd."   "JPN" "Asia Pacific" "TOK" "JPN" "2016-11-30" "1" 2 "quandl"
587 "" "A" "1.2.1" "2008-12-31"  92827 1817627648 "GBP" "Devro Plc (United Kingdom)"     "GBR" "Europe"       "NL"  "GBR" "2016-12-31" "1" 2 "google"
589 "" "A" "1.2.1" "2009-02-28"  15771 1834419968 "EUR" "Suedzucker Aktiengesellschaf"   "DEU" "Europe"       "NL"  "DEU" "2017-02-28" "1" 2 "google"
590 "" "A" "1.8.2" "2009-03-31"  52058 1834427904 "JPY" "Shiroki Corp. (Japan)"          "JPN" "Asia Pacific" "NL"  "JPN" "2015-03-31" "1" 2 "quandl"
591 "" "A" "1.2.1" "2009-04-04"  83641 1834428928 "GBP" "Wiseman (Robert) Dairies PLC"   "GBR" "Europe"       "NL"  "GBR" "2015-12-31" "1" 2 "google"
593 "" "A" "1.1.1" "2009-06-30"  88449 1834451200 "EUR" "KWS SAAT AG (Germany)"          "DEU" "Europe"       "NL"  "DEU" "2017-06-30" "1" 2 "google"
595 "" "A" "1.4.2" "2009-08-31" 130905 1834467072 "JPY" "Nagaileben Co Ltd Tokyo"        "JPN" "Asia Pacific" ""    "JPN" "2015-08-31" "1" 2 "quandl"
596 "" "A" "1.7.1" "2009-09-20"  97127 1834472192 "JPY" "Noevir Holdings Co., Ltd."      "JPN" "Asia Pacific" "TOK" "JPN" "2016-09-30" "1" 2 "quandl"
598 "" "A" "1.4.1" "2009-11-30"  85521 1834490368 "JPY" "Japan Wool Textile Co., Ltd."   "JPN" "Asia Pacific" "TOK" "JPN" "2016-11-30" "1" 2 "quandl"
599 "" "A" "1.1.1" "2009-12-31"  74589 1834498304 "USD" "Anglo Eastern Plantations PL"   "GBR" "Europe"       "NL"  "GBR" "2016-12-31" "1" 2 "google"
600 "" "A" "1.4.2" "2010-01-31" 131824 1851283456 "EUR" "Gruppo Coin Spa"                "ITA" "Europe"       ""    "ITA" "2016-01-31" "1" 2 "google"
601 "" "A" "1.2.1" "2010-02-28"  15771 1851290624 "EUR" "Suedzucker Aktiengesellschaf"   "DEU" "Europe"       "NL"  "DEU" "2017-02-28" "1" 2 "google"
602 "" "A" "1.8.2" "2010-03-31" 100385 1851298560 "JPY" "Imasen Electric Industrial Co." "JPN" "Asia Pacific" "TOK" "JPN" "2017-03-31" "1" 2 "quandl"
603 "" "A" "1.2.1" "2010-04-03"  83641 1851299328 "GBP" "Wiseman (Robert) Dairies PLC"   "GBR" "Europe"       "NL"  "GBR" "2015-12-31" "1" 2 "google"
605 "" "A" "1.1.1" "2010-06-30"  88449 1851321856 "EUR" "KWS SAAT AG (Germany)"          "DEU" "Europe"       "NL"  "DEU" "2017-06-30" "1" 2 "google"
607 "" "A" "1.4.2" "2010-08-31" 130905 1851337728 "JPY" "Nagaileben Co Ltd Tokyo"        "JPN" "Asia Pacific" ""    "JPN" "2015-08-31" "1" 2 "quandl"
608 "" "A" "1.6.1" "2010-09-30" 133218 1851345408 "EUR" "Derby Cycle AG"                 "DEU" "Europe"       "NL"  "DEU" "2012-09-30" "1" 2 "google"
610 "" "A" "1.4.1" "2010-11-30"  85521 1851361024 "JPY" "Japan Wool Textile Co., Ltd."   "JPN" "Asia Pacific" "TOK" "JPN" "2016-11-30" "1" 2 "quandl"
611 "" "A" "1.2.2" "2010-12-31" 101492 1851368960 "EUR" "Hawesko Holding AG"             "DEU" "Europe"       "FSE" "DEU" "2016-12-31" "1" 2 "google"
612 "" "A" "1.4.2" "2011-01-31" 131824 1868154112 "EUR" "Gruppo Coin Spa"                "ITA" "Europe"       ""    "ITA" "2016-01-31" "1" 2 "google"
613 "" "A" "1.2.1" "2011-02-28" 114174 1868161280 "JPY" "S Foods Inc"                    "JPN" "Asia Pacific" "TOK" "JPN" "2017-02-28" "1" 2 "quandl"
614 "" "A" "1.2.1" "2011-03-31" 114219 1868169216 "JPY" "J-Oil Mills Inc"                "JPN" "Asia Pacific" "TOK" "JPN" "2017-03-31" "1" 2 "quandl"
615 "" "A" "1.2.1" "2011-04-02"  83641 1868169728 "GBP" "Wiseman (Robert) Dairies PLC"   "GBR" "Europe"       "NL"  "GBR" "2015-12-31" "1" 2 "google"
617 "" "A" "1.1.1" "2011-06-30"  88449 1868192512 "EUR" "KWS SAAT AG (Germany)"          "DEU" "Europe"       "NL"  "DEU" "2017-06-30" "1" 2 "google"
619 "" "A" "1.4.2" "2011-08-31" 130905 1868208384 "JPY" "Nagaileben Co Ltd Tokyo"        "JPN" "Asia Pacific" ""    "JPN" "2015-08-31" "1" 2 "quandl"
620 "" "A" "1.7.1" "2011-09-30"  97127 1868216064 "JPY" "Noevir Holdings Co., Ltd."      "JPN" "Asia Pacific" "TOK" "JPN" "2016-09-30" "1" 2 "quandl"
622 "" "A" "1.4.1" "2011-11-30"  85521 1868231680 "JPY" "Japan Wool Textile Co., Ltd."   "JPN" "Asia Pacific" "TOK" "JPN" "2016-11-30" "1" 2 "quandl"
623 "" "A" "1.2.1" "2011-12-31"  70268 1868239616 "EUR" "Campofrio Food Group SA"        "ESP" "Europe"       "NL"  "ESP" "2016-12-31" "1" 2 "google"
624 "" "A" "1.2.1" "2012-01-28"  83641 1885024000 "GBP" "Wiseman (Robert) Dairies PLC"   "GBR" "Europe"       "NL"  "GBR" "2015-12-31" "1" 2 "google"
625 "" "A" "1.2.1" "2012-02-29" 114174 1885032192 "JPY" "S Foods Inc"                    "JPN" "Asia Pacific" "TOK" "JPN" "2017-02-28" "1" 2 "quandl"
626 "" "A" "1.4.1" "2012-03-31"  90674 1885040128 "INR" "Vardhman Textiles"              "IND" "Asia Pacific" "NL"  "IN"  "2015-03-31" "1" 2 "google"
627 "" "A" "1.2.2" "2012-04-02"  91919 1885040640 "GBP" "Majestic Wine Plc (United Ki"   "GBR" "Europe"       "NL"  "GBR" "2017-04-03" "1" 2 "google"
629 "" "A" "1.1.1" "2012-06-30"  88449 1885063424 "EUR" "KWS SAAT AG (Germany)"          "DEU" "Europe"       "NL"  "DEU" "2017-06-30" "1" 2 "google"
631 "" "A" "1.4.2" "2012-08-31" 130905 1885079296 "JPY" "Nagaileben Co Ltd Tokyo"        "JPN" "Asia Pacific" ""    "JPN" "2015-08-31" "1" 2 "quandl"
632 "" "A" "1.6.1" "2012-09-30" 133218 1885086976 "EUR" "Derby Cycle AG"                 "DEU" "Europe"       "NL"  "DEU" "2012-09-30" "1" 2 "google"
634 "" "A" "1.4.1" "2012-11-30"  85521 1885102592 "JPY" "Japan Wool Textile Co., Ltd."   "JPN" "Asia Pacific" "TOK" "JPN" "2016-11-30" "1" 2 "quandl"
635 "" "A" "1.2.2" "2012-12-31" 102171 1885110528 "EUR" "Baron de Ley SA"                "ESP" "Europe"       "NL"  "ESP" "2016-12-31" "1" 2 "google"
637 "" "A" "1.2.1" "2013-02-28"  92859 1901902848 "EUR" "LDC SA (France)"                "FRA" "Europe"       "NL"  "FRA" "2017-02-28" "1" 2 "google"
638 "" "A" "1.8.2" "2013-03-31" 100277 1901910784 "JPY" "Yutaka Giken Co., Ltd. (Japa"   "JPN" "Asia Pacific" "JAS" "JPN" "2017-03-31" "1" 2 "quandl"
639 "" "A" "1.2.2" "2013-04-01"  91919 1901911040 "GBP" "Majestic Wine Plc (United Ki"   "GBR" "Europe"       "NL"  "GBR" "2017-04-03" "1" 2 "google"
641 "" "A" "1.1.1" "2013-06-30"  88449 1901934080 "EUR" "KWS SAAT AG (Germany)"          "DEU" "Europe"       "NL"  "DEU" "2017-06-30" "1" 2 "google"
643 "" "A" "1.6.1" "2013-08-31"  84695 1901949952 "EUR" "Beneteau S.A."                  "FRA" "Europe"       "NL"  "FRA" "2016-08-31" "1" 2 "google"
644 "" "A" "1.7.1" "2013-09-30"  97127 1901957632 "JPY" "Noevir Holdings Co., Ltd."      "JPN" "Asia Pacific" "TOK" "JPN" "2016-09-30" "1" 2 "quandl"
646 "" "A" "1.4.1" "2013-11-30"  85521 1901973248 "JPY" "Japan Wool Textile Co., Ltd."   "JPN" "Asia Pacific" "TOK" "JPN" "2016-11-30" "1" 2 "quandl"
647 "" "A" "1.2.1" "2013-12-31"  83641 1901981185 "GBP" "Wiseman (Robert) Dairies PLC"   "GBR" "Europe"       "NL"  "GBR" "2015-12-31" "1" 2 "google"
649 "" "A" "1.2.1" "2014-02-28" 114174 1918773504 "JPY" "S Foods Inc"                    "JPN" "Asia Pacific" "TOK" "JPN" "2017-02-28" "1" 2 "quandl"
650 "" "A" "1.4.1" "2014-03-31"  90102 1918781440 "INR" "Himatsingka Seide"              "IND" "Asia Pacific" "NL"  "IN"  "2015-03-31" "1" 2 "google"
653 "" "A" "1.1.1" "2014-06-30"  88449 1918804736 "EUR" "KWS SAAT AG (Germany)"          "DEU" "Europe"       "NL"  "DEU" "2017-06-30" "1" 2 "google"
655 "" "A" "1.6.1" "2014-08-31"  84695 1918820608 "EUR" "Beneteau S.A."                  "FRA" "Europe"       "NL"  "FRA" "2016-08-31" "1" 2 "google"
656 "" "A" "1.7.1" "2014-09-30"  97127 1918828288 "JPY" "Noevir Holdings Co., Ltd."      "JPN" "Asia Pacific" "TOK" "JPN" "2016-09-30" "1" 2 "quandl"
658 "" "A" "1.4.1" "2014-11-30"  85521 1918843904 "JPY" "Japan Wool Textile Co., Ltd."   "JPN" "Asia Pacific" "TOK" "JPN" "2016-11-30" "1" 2 "quandl"
659 "" "A" "1.2.1" "2014-12-31"  70268 1918851840 "EUR" "Campofrio Food Group SA"        "ESP" "Europe"       "NL"  "ESP" "2016-12-31" "1" 2 "google"
660 "" "A" "1.4.2" "2015-01-31" 131824 1935636992 "EUR" "Gruppo Coin Spa"                "ITA" "Europe"       ""    "ITA" "2016-01-31" "1" 2 "google"
661 "" "A" "1.2.1" "2015-02-28" 114174 1935644160 "JPY" "S Foods Inc"                    "JPN" "Asia Pacific" "TOK" "JPN" "2017-02-28" "1" 2 "quandl"
662 "" "A" "1.2.2" "2015-03-31"  74246 1935652096 "JPY" "Takara Holdings Inc"            "JPN" "Asia Pacific" ""    "JPN" "2017-03-31" "1" 2 "quandl"
end
format %tmNN/CCYY date
label values sectnum sectnum
label def sectnum 2 "1", modify


keep if sector == "1" //temporary I will loop through sectors on the real item
gsort +ticker date

preserve
clear
tempfile collect
save `collect', emptyok
restore, preserve

levelsof web, local(websites)
foreach w of local websites {
        levelsof ticker if web == "`w'", local(tick`w')
        }

restore, preserve
foreach t of local tickquandl {
    capture noisily{

    getsymbols `t', fm(12) fd(1) fy(1989) lm(12) ld(31) ly(2018) database("TSE") apikey(gwUUBSKmZwvJWbSzZ2mh) frequency(m) price (    close) clear
    
    append using `collect'
    
    save `"`collect'"', replace
    
    }

}    
foreach t of local tickgoogle {
    
    capture noisily{
    
    getsymbols `t', fm(12) fd(31) fy(1989) lm(12) ld(31) ly(2018) frequency(m) price (close) clear google
  
    
    append using `collect'
    
    save `"`collect'"', replace
    
    }    
    
}


use `collect', clear
drop date
rename period date
duplicates drop ticker date, force
save `"`collect'"', replace
// fix the date format
restore
 
merge 1:1 date ticker using `building', nogenerate

drop if missing(country)
drop if close == .
encode ticker, gen(n_ticker)
xtset n_ticker date, monthly
gen return = 100*(F12.close - close)/close

order date ticker close n_ticker return, last

[Phil Bromiley]

I appreciate your posting the right material. However, you need to break this down. You can't expect anyone to work through a ten item list to try to figure out what you're doing and what you want to do. You may need to contact the author of getsymbols.

For most problems like this, I download a pile of data from something like WRDS and then load it all into Stata for analysis. I don't know if quandl has this option, but if it does it might be easier.

Instead of writing a long program and trying to make it all work, start with a little bit, make it work and then move on. If you've got problems with getsymbols, run it outside the loop with the problematic values of tickgoogle to see what it does. You can also use display to make sure the locals (t and collect) contain what you intend. You can run with set trace on to see exactly where and how it is crashing. Then, after you've found exactly what hang up is, you can ask us for help and we can provide it.

[Giles German]

Thanks Phil that's great advice. I have managed to work through this now and have it working. I will try and break this down more in future. It's been a big problem for me as the data set is 5million observations and I am running individual tickers through loops to test it. I'm wasting hours on testing as I am wrestling with getting an accurate representative data set that will test the commands correctly so when i put the actual data in it tests it accurately...

I use the dataex function as recommended on the forum is there any way of getting dataex to do a random sample of my data set for testing purposes which will work when i launch the actual data back in?

[William Lisowski]

The dataex command allows use of the if qualifier, so if you create a variable that identifies observations in a testing sample, you can do something like

dataex x1 x2 x3 x4 if for_testing==1

You almost certainly don't want a random sample, you want a purposive sample that restricts itself to, for example, 2 sectors, 2 websites, ... .

[Giles German]

I am trying to encode the sector number but when I run the macro with the following data and code it should read "7" but it seems to think it is "1". Can you help please?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str5 mic str7 ticker str3 exchange
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
"7.5.5" "DLAR" "LON"
end

gen firstdot = strpos(mic,".")
gen sector = substr(mic,1,firstdot-1)
encode sector, generate (sectnum)
drop firstdot


levelsof sectnum, local(sectnum)