Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Different variables with different dates

    Hello,

    I want to make the portfolio sort analysis and extract the idiosyncratic volatility,
    However, I had to structure the dataset myself because there is no database with cryptocurrencies as well as my market return proxy.
    When datasets are merged I have 5 years of returns for each crypto and 5 years for market return but it is only one variable so for other dates of crypto the return has a missing value.
    I was wondering what would be an adequate solution to this problem in order to run asreg on a rolling window basis or MacBeth analysis?
    I post the piece of the picture of what it looks like bellow.

    Thank You for Your time.
    Attached Files

  • #2
    I still have not found the answer anywhere on the internet
    I would greatly appreciate any help

    Thank you

    Comment


    • #3
      Your post has gone a few days without a response. Simply stating that your question remains open is unlikely to prove useful. When a question goes unanswered here for a long period of time it usually means that a) the question is unclearly posed or lacking needed information, or b) nobody who has read the post knows the answer.

      In this case it may be a mix of both. It is unclear exactly what you mean by "it is only one variable." What does the "it" refer to?

      Moreover, the organization of the example data set you show is quite strange. Each observation has two date variables that have no apparent relationship to each other. And there is no indication of how either of them is related to any of the other variables. It looks like the dataset was created by slapping together some unrelated blocks of data without regard to how observations in one relate to the other. Perhaps there is method to the madness here, but it is not apparent what that might be.

      So I suggest you back up and explain more clearly what your problem is. In particular, show actual Stata example data from the data sets that were merged. Use the -dataex- command to do that. Do not use screenshots or list output or any other method for showing the example data. Then show the actual -merge- command that you used to put them together. Then show a mock-up of what kind of result you would like to have.

      If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

      Finally, if it ultimately boils down to their simply being no appropriate data for DATE and MarketIndex in the observations where those are missing, your question about how to impute values may be a substantive finance question rather than a statistical one. In that case, you would be better advised to consult somebody in your discipline, not a statistics and Stata forum.

      Comment


      • #4
        Thank you for the quick response.

        There are two datasets. One includes 5 years of daily crypto returns. The other one includes 5 years of market index daily returns. This is the dataset with only MarketIn

        dex and its date
        Code:
        * Example generated by -dataex-. For more info, type help dataex
        clear
        input strL DATE double MarketIndex
        " 7/17/2017" 260.39
        "12/14/2018" 268.73
        " 7/19/2017" 273.71
        " 7/14/2017" 274.03
        "12/13/2018" 275.97
        " 7/11/2017" 277.82
        "12/11/2018"  284.5
        " 12/7/2018" 284.87
        " 7/13/2017" 288.54
        "12/10/2018" 290.27
        "12/12/2018" 293.49
        "  2/6/2019" 297.16
        "  2/7/2019" 297.78
        " 7/26/2017" 298.55
        " 7/25/2017" 298.92
        " 1/29/2019" 299.35
        " 1/28/2019" 300.41
        " 6/26/2017" 300.41
        " 7/10/2017" 302.88
        " 7/18/2017" 303.56
        "  2/5/2019" 303.74
        "  2/4/2019"  303.9
        " 1/31/2019" 303.99
        " 7/12/2017" 304.03
        "12/18/2018" 304.33
        "  2/1/2019" 306.21
        " 7/27/2017"  306.7
        " 1/30/2019" 306.79
        "12/17/2018"  307.1
        " 12/6/2018" 310.56
        " 7/28/2017" 314.64
        " 1/21/2019" 317.93
        " 1/23/2019" 318.55
        " 7/21/2017" 318.71
        " 1/15/2019"  319.2
        " 1/25/2019" 319.29
        "12/27/2018" 320.68
        " 2/14/2019" 321.08
        " 2/13/2019" 321.56
        " 2/15/2019" 321.72
        " 1/24/2019" 321.82
        " 1/16/2019" 323.73
        " 1/22/2019" 323.81
        " 7/20/2017" 323.81
        " 1/18/2019" 323.98
        " 7/31/2017" 323.99
        " 12/5/2018" 324.16
        " 2/11/2019" 324.24
        " 6/27/2017" 324.84
        "  8/2/2017" 325.19
        "  2/8/2019" 325.48
        "11/26/2018" 326.06
        " 2/12/2019" 326.82
        "12/19/2018" 326.95
        " 1/17/2019" 327.17
        " 1/10/2019" 328.67
        "11/27/2018" 329.47
        " 1/11/2019" 329.59
        "  7/7/2017" 329.87
        " 1/14/2019" 331.58
        "  8/1/2017" 334.22
        " 12/3/2018" 335.22
        "  8/3/2017" 335.28
        "12/31/2018" 335.42
        "12/21/2018" 335.58
        "  3/4/2019" 336.15
        "  8/4/2017" 336.76
        " 7/24/2017" 337.16
        " 12/4/2018" 337.32
        " 2/27/2019" 337.98
        " 6/15/2017" 342.41
        "12/26/2018" 342.59
        "12/20/2018" 343.92
        "  1/1/2019"    344
        "12/25/2018" 345.77
        "  7/6/2017" 346.45
        " 6/30/2017" 346.46
        "  7/4/2017" 346.75
        " 2/26/2019" 347.03
        "  7/3/2017" 347.07
        " 2/28/2019" 347.95
        "11/30/2018" 348.57
        " 2/25/2019" 350.23
        "  1/3/2019" 351.42
        "  7/5/2017" 351.78
        "  3/1/2019" 352.26
        "  1/4/2019" 352.93
        "  3/5/2019" 352.94
        "  6/8/2017" 353.02
        " 3/11/2019" 353.46
        "  3/6/2019" 354.17
        " 2/18/2019" 355.66
        "12/28/2018" 356.74
        " 3/12/2019"  356.8
        " 3/13/2019" 356.82
        " 6/29/2017" 357.18
        " 3/14/2019" 357.27
        " 2/21/2019" 357.42
        "  3/7/2019"    358
        "  3/8/2019" 359.56
        end

        And this is the data with cryptocurrencies, their dates as well as their ids as I did the code egen id=group(Cryptocurrency)



        Code:
        * Example generated by -dataex-. For more info, type help dataex
        clear
        input str13 Cryptocurrency double DailyRET float(id date datem)
        "Close1ECO" 2.59982775311351 1 22646 744
        "Close1ECO"  2.6923183401435 1 22677 745
        "Close1ECO" 2.88810466787037 1 22705 746
        "Close1ECO" 7.26674053571583 1 22736 747
        "Close1ECO" 6.20956307859274 1 22766 748
        "Close1ECO" 2.69779346646118 1 22647 744
        "Close1ECO" 2.68900610873525 1 22678 745
        "Close1ECO" 3.96117946060583 1 22706 746
        "Close1ECO" 7.30301584415523 1 22737 747
        "Close1ECO" 6.23159382873996 1 22767 748
        "Close1ECO" 2.69931274845962 1 22648 744
        "Close1ECO" 2.67210174167999 1 22679 745
        "Close1ECO" 4.00532044605378 1 22707 746
        "Close1ECO" 7.30200366373626 1 22738 747
        "Close1ECO" 6.23950582004669 1 22768 748
        "Close1ECO" 2.69461326035833 1 22649 744
        "Close1ECO"  2.8017095053978 1 22680 745
        "Close1ECO" 4.00762556873101 1 22708 746
        "Close1ECO" 7.30053958081247 1 22739 747
        "Close1ECO" 6.21354495316608 1 22769 748
        "Close1ECO" 2.69534641400447 1 22650 744
        "Close1ECO" 2.79908724541904 1 22681 745
        "Close1ECO"  3.9709336460899 1 22709 746
        "Close1ECO" 9.16929744503867 1 22740 747
        "Close1ECO"  6.1991092318914 1 22770 748
        "Close1ECO" 2.69806058436293 1 22651 744
        "Close1ECO"  2.7724798605898 1 22682 745
        "Close1ECO" 3.85859948803128 1 22710 746
        "Close1ECO" 9.40947568762626 1 22741 747
        "Close1ECO" 5.74915016054537 1 22771 748
        "Close1ECO" 2.69718296358042 1 22652 744
        "Close1ECO" 2.80029723784339 1 22683 745
        "Close1ECO" 5.15427007257239 1 22711 746
        "Close1ECO" 9.22082243181356 1 22742 747
        "Close1ECO" 5.73283023196484 1 22772 748
        "Close1ECO" 2.68776880097017 1 22653 744
        "Close1ECO" 2.79419733256025 1 22684 745
        "Close1ECO" 5.20489417613213 1 22712 746
        "Close1ECO" 9.38986787841923 1 22743 747
        "Close1ECO" 5.68948422266834 1 22773 748
        "Close1ECO" 2.69981610116035 1 22654 744
        "Close1ECO" 2.77124889256655 1 22685 745
        "Close1ECO"   5.149604515676 1 22713 746
        "Close1ECO" 9.36677892849372 1 22744 747
        "Close1ECO" 5.53056383025764 1 22774 748
        "Close1ECO"  2.6991279208797 1 22655 744
        "Close1ECO" 2.79618736359451 1 22686 745
        "Close1ECO" 5.14583865048242 1 22714 746
        "Close1ECO" 9.41540195152697 1 22745 747
        "Close1ECO" 5.44903604357575 1 22775 748
        "Close1ECO" 2.69979827070181 1 22656 744
        "Close1ECO" 2.80578047898185 1 22687 745
        "Close1ECO" 5.08598267406945 1 22715 746
        "Close1ECO" 9.41284021980847 1 22746 747
        "Close1ECO" 5.10875796033097 1 22776 748
        "Close1ECO"  2.6990985646001 1 22657 744
        "Close1ECO" 2.79822392527676 1 22688 745
        "Close1ECO" 5.72641150509418 1 22716 746
        "Close1ECO" 9.40963684038469 1 22747 747
        "Close1ECO" 4.67537545084626 1 22777 748
        "Close1ECO" 2.70119763394482 1 22658 744
        "Close1ECO" 2.76164745869127 1 22689 745
        "Close1ECO" 5.72579051603429 1 22717 746
        "Close1ECO" 9.34062020281813 1 22748 747
        "Close1ECO" 4.14053713327709 1 22778 748
        "Close1ECO" 2.68719883892333 1 22659 744
        "Close1ECO" 2.75075966099189 1 22690 745
        "Close1ECO"  5.7295317032804 1 22718 746
        "Close1ECO" 9.41464642368766 1 22749 747
        "Close1ECO" 4.13754690042003 1 22779 748
        "Close1ECO" 2.70079344483837 1 22660 744
        "Close1ECO" 2.66589446295236 1 22691 745
        "Close1ECO" 5.67580458954427 1 22719 746
        "Close1ECO" 9.40995710490792 1 22750 747
        "Close1ECO"  4.1686072667777 1 22780 748
        "Close1ECO" 2.69949915123752 1 22661 744
        "Close1ECO" 2.73199013142452 1 22692 745
        "Close1ECO" 5.73320747126235 1 22720 746
        "Close1ECO" 9.38819844560188 1 22751 747
        "Close1ECO" 4.14584681991441 1 22781 748
        "Close1ECO" 2.69801296760392 1 22662 744
        "Close1ECO" 2.76198991856655 1 22693 745
        "Close1ECO" 5.72991220350507 1 22721 746
        "Close1ECO" 9.39093275113673 1 22752 747
        "Close1ECO" 4.14001660600795 1 22782 748
        "Close1ECO" 2.69683134188394 1 22663 744
        "Close1ECO"  2.7898677737455 1 22694 745
        "Close1ECO" 5.73094859608694 1 22722 746
        "Close1ECO" 9.41972914825545 1 22753 747
        "Close1ECO" 4.13258246466991 1 22783 748
        "Close1ECO" 2.69631205856595 1 22664 744
        "Close1ECO" 2.77222898972554 1 22695 745
        "Close1ECO" 5.73500180472082 1 22723 746
        "Close1ECO" 9.40882260750592 1 22754 747
        "Close1ECO" 4.14710803234604 1 22784 748
        "Close1ECO" 2.69682841567787 1 22665 744
        "Close1ECO"  2.8660481761631 1 22696 745
        "Close1ECO" 5.73379234103526 1 22724 746
        "Close1ECO" 9.39563019020921 1 22755 747
        "Close1ECO" 4.09561524043299 1 22785 748
        end
        format %td date
        format %tm datem
        After I have done egen id = group(Cryptocurrency) I have created an id for each crypto so I can run the regressions after, however, what I need now is to also have market return for every day.
        In otherwords I need MarketIndex to duplicate and show for every observation in the sample where for every cryptocurrency day. 5 years of marketindex for 1 cryptocurrency, 5 years of MarketIndex for another etc.

        I hope it makes more sense now



        Last edited by Simonas Raipa; 14 Jun 2022, 11:13.

        Comment


        • #5
          Well, it is becoming clearer.

          1. The date variable in the market returns data set is not a Stata internal format date variable, it is a string, and it needs to be converted before you can join these data sets in any sensible way.

          Code:
          gen date = daily(DATE, "MDY")
          assert missing(date) == missing(DATE)
          format date %td
          drop DATE
          isid date, sort
          2. I do not know how you selected these example data to show (or if you just used the -dataex- default of the 1st 100 observations in each.) But the market data example has dates between 2017 and 2019 (and while they are in daily-date format, there appear to be large and unsystematic gaps between dates in consecutive observations). By contrast, the crypto data set shows consecutive day dates, but all in 2022. Perhaps these problems do not exist in the full data sets. But if they do, there is no hope of getting complete matching between these data sets.

          3. You don't say how you tried to put these two data sets together. You say you merged them, but you don't show the exact command, so that leaves a great deal to the imagination. If you truly have five years of daily data in both data sets (and it is the same five years in both) then you should be able to match them up easily with the following commands:
          Code:
          use market_data_set, clear
          gen date = daily(DATE, "MDY")
          assert missing(date) == missing(DATE)
          format date %td
          drop DATE
          isid date, sort
          
          merge 1:m date using crypto_data_set
          In any case, if you use the code suggested immediately above, any unmatched observations will be due to actual gaps in one or both data sets. If the data sets don't have the exact same dates in them, then such mismatches will be unavoidable. If the number of mismatches is large, then you should diligently search for a better data source.

          Comment


          • #6
            Yes indeed I have used the default version of -dataex- and I have not rearranged the dates in the marketdata set. They have the same 5 years of data with several missing days.

            I tried merging them with the options menu since I was not familiar with the merging codes and was not able to find them online. I know it is a small thing but it really was difficult for me to have a grasp on it.

            Your method has worked like a charm.
            Thank you very much

            Comment

            Working...
            X