Even Vectors out - Panel Data

**Natarajan95** · 04-25-2019, 03:21 AM

Hi guys,
I am writing my thesis on an extensive panel data. I need quarterly data for the period 2011-2018, however it is not possible to specify this from the data source. Therefore, I have about 70.000 rows that i would have to even out manually so the vectors in my panel are of equal length - I am guessing there is a smarter way.
Excel_Forum_Help.PNG

From the data source i retrieve the name, time and price. But i only get data for the points in which changes were made. Therefore, the lengths are problematic.
- Do you guys know a way to even out the 14 data points on the left so it corresponds to the 32 points on the right? I am guessing It should be something like:
Insert rows where there is no observation and if there is not data for this point, it should take the above point. So for example for the 3rd quarter of 2012, I should insert a row and say the price was 2,14.

Best regards

Patrick

**myall_blues** · 04-25-2019, 05:44 AM

I have a few questions about your data and description first.

Because you 'only get data for the points in which changes were made' why are there repeated values in the list of prices?

Secondly, how should the values be mapped from the 14 to the 32 (in the case of the example). For example, why is Q2 2012 2.14 but Q3 2012 2.13? It doesn't appear to be a simple case of taking the point above unless I have misunderstood.

Regards

Murray

**Natarajan95** · 04-25-2019, 06:05 AM

Hi Murray,
I see, the reason for repeated values would be due to small changes which are not included here (3rd decimal or beyond).

In regards to the mapping, you are right. The second quarter of 2012 should be 2,13. I basically translated dates to quarters with: "Q"&INT((MONTH(D2)-1)/3)+1&"-"&YEAR(D2).
But in this case for example, i only have one observation for 2011, which is Q4-2011. In that case, i would need to insert three rows above with the same value as in Q4-2011, 2,14.

Best Regards

Patrick

**AliGW** · 04-25-2019, 06:13 AM

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.