-2

I am working on a research portal for a client, they have their data in an excel workbook and each sheet containing stock prices for a particular year. There are about 11 worksheets (each representing a year i.e sheet 2008 - 2019). To keep things simple, each sheet has a column for Company Symbol Ticker, then other series of columns each representing a trading day. So it looks like (Symbol Ticker, 04 Jan 2008, 05 Jan 2008,06 Jan 2008,..,31 Dec 2008). I want to store this data in a database but dont want to have that huge freaking number of columns in the database one major reason being that the trading days is not the same for all sheets(years).

So how i want to store this data is have a date column in the database, values column , and the company's column , so basically (date,value,companyID).

I have transposed the table and placed the companies IDs (1,2,3,...) on each corresponding value columns to identify the company that has values in a particular column.

The next step now is to arrange these data in a 3 column (date,value,companyID) so that i can convert to CSV and import into the database. I know an excel macro can handle this very sure but cant figure out how to go about it as i am still a beginner in VBA. Thanks for your help in advance.

              1  2   3
02-Jan-14    0.50    44.69   39.00 
03-Jan-14    0.50    44.01   39.00 
06-Jan-14    0.50    43.50   39.00 
07-Jan-14    0.50    43.66   37.50 
08-Jan-14    0.50    43.99   39.00 
09-Jan-14    0.50    44.00   39.00 
10-Jan-14    0.50    44.00   40.00 
13-Jan-14    0.50    43.50   40.01 
15-Jan-14    0.50    43.99   41.00 
16-Jan-14    0.50    44.13   41.06 
17-Jan-14    0.50    44.25   41.06 
20-Jan-14    0.50    44.39   41.06 
21-Jan-14    0.50    44.39   41.06 
22-Jan-14    0.50    44.39   43.11

Expected result

02-Jan-14    0.50   1
03-Jan-14    0.50   1
06-Jan-14    0.50   1
07-Jan-14    0.50   1
08-Jan-14    0.50   1
09-Jan-14    0.50   1
10-Jan-14    0.50   1
13-Jan-14    0.50   1
15-Jan-14    0.50   1
16-Jan-14    0.50   1
17-Jan-14    0.50   1
20-Jan-14    0.50   1
21-Jan-14    0.50   1
22-Jan-14    0.50   1

02-Jan-14    44.69  2
03-Jan-14    44.01  2
06-Jan-14    43.50  2
07-Jan-14    43.66  2
08-Jan-14    43.99  2
09-Jan-14    44.00  2
10-Jan-14    44.00  2
13-Jan-14    43.50  2
15-Jan-14    43.99  2
16-Jan-14    44.13  2
17-Jan-14    44.25  2
20-Jan-14    44.39  2
21-Jan-14    44.39  2
22-Jan-14    44.39  2

02-Jan-14    39.00  3
03-Jan-14    39.00  3
06-Jan-14    39.00  3
07-Jan-14    37.50  3
08-Jan-14    39.00  3
09-Jan-14    39.00  3
10-Jan-14    40.00  3
13-Jan-14    40.01  3
15-Jan-14    41.00  3
16-Jan-14    41.06  3
17-Jan-14    41.06  3
20-Jan-14    41.06  3
21-Jan-14    41.06  3
22-Jan-14    43.11  3
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Chukwuezugo Okpara
  • 52
  • 9
  • The keyword to google for is *"Unpivot"*. Give it a try yourself and if you get stuck or errors come back showing your code. – Pᴇʜ May 10 '19 at 06:11

1 Answers1

0

Yeaaaahhhhh!!!...Thanks alot @PEH , it was actually an "Unpivot" problem as you suggested and i so appreciate. After hours of googling , i found this link that answered my question :) Convert Matrix to 3 column table reverse pivot unpivot

Thanks once again PEH

Chukwuezugo Okpara
  • 52
  • 9