How to separate my data

Question

I have headers of"Company Number", "Stock Ticker",&nbsp; "Date", "Price"Currently each row is one day, I have 15 years worth of data.After the 10&nbsp; years of prices are listed for one stock, the next company's stock immediately follows, and starts again from 2003 each row until 2018Then this happens for the next stock etc...I have like 400 stocks.&nbsp;First, I want to remove all data points for any stock ticker that isn't in a certain list of tickers that I haveThen I want to move data for each stock to a separate page in excel, how do I do this easily?

jwr1138 · Answer

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.&nbsp;

daniel0 · Answer

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 · Answer

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 · Answer

That sounds good, I'll attach a copy of the data so I can explain what I'm trying to achieve.&nbsp;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.&nbsp;I first want to clean the data.I want to use only companies that have data for 30/06/2003-30/06-2018This 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.&nbsp;Also, sometime there are letters in the "Returns" column. Can we remove all companies that have at least one non-number in that column?&nbsp;I would like one page with a list of permno and all tickers/company names that are associated with that permno.&nbsp;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.&nbsp;Before creating a&nbsp;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

Forum Discussion

How to separate my data

Share

Resources