Forum Discussion

Daniel0's avatar
Daniel0
Copper Contributor
Dec 17, 2018

How to separate my data

I have headers of "Company Number", "Stock Ticker",  "Date", "Price" Currently each row is one day, I have 15 years worth of data. After the 10  years of prices are listed for one stock, the next ...
BI & Data Analysis
excel
Macros and VBA
JWR1138's avatar
JWR1138
Iron Contributor
Dec 17, 2018

This seems pretty doable in VBA, are you sure you want this as 400 separate worksheets in one workbook though? that seems like it would be quite a pain to work with. 

  • Daniel0's avatar
    Daniel0
    Copper Contributor
    Dec 17, 2018

    I'm not familiar with VBA at all, how could I do that?

    I would like all of those pages so it is easy for me to look at companies individually, because scrolling through 1,000,000 rows is a pain.

    But when I clean all of the data, I would eventually want like the rows be the date, and the columns be the ticker, both headed.

     

    • JWR1138's avatar
      JWR1138
      Iron Contributor
      Dec 17, 2018

      I'll help you with whichever way you want to do this, I think you'd be better off keeping all the data on one sheet though and just having another sheet where you input the stock symbol and it displayed the data for just that stock, would that work for you?

      • Daniel0's avatar
        Daniel0
        Copper Contributor
        Dec 18, 2018

        That sounds good, I'll attach a copy of the data so I can explain what I'm trying to achieve.

         

        The permno is used to identify a company, even if a company changes its name or ticker, this stays the same.

        Instead of prices I have gone with returns and market cap.

         

        I first want to clean the data.

        I want to use only companies that have data for 30/06/2003-30/06-2018

        This is strictly 3778 data points per company, but to be safe let's say that if a permno appears less than 3750 times in the perno column, get rid of all rows with that perno number in the permo column.

         

        Also, sometime there are letters in the "Returns" column. Can we remove all companies that have at least one non-number in that column?

         

        I would like one page with a list of permno and all tickers/company names that are associated with that permno.

         

        Your idea of a searchable page is amazing. If I just type in the permno somewhere and it shows all of the results in order for that company, that would be great.

         

        Before creating a final single-page data set, I would need to check the cleaned data first.

        I take back the requirements in my first few posts, the layout in the attached document is fine, all I need is permno, date (in some number format), returns and cap. I think I can figure that out myself

Resources