Hi all,
I have a massive matrix of pricing data (a sample below) in excel that I would like to import into and use in Access. The horizontal headers are "tickers" and the vertical are dates.
<tbody>
</tbody>
I am not sure how to use the data in this form, and I assume it is better to have the tickers as IDs (so they can be linked to another table with more ticker data), as below:
<tbody>
</tbody>
Meaning the other table could be as follows:
<tbody>
</tbody>
Given the size of this matrix, however, I don't want to have to rearrange it manually. Is there a way to rearrange the data such using SQL (or any other means)?
Thanks
I have a massive matrix of pricing data (a sample below) in excel that I would like to import into and use in Access. The horizontal headers are "tickers" and the vertical are dates.
Date | ACL SJ Equity | BAT SJ Equity | CSB SJ Equity | GRF SJ Equity |
1/2/2014 | 3764 | 658 | 12315 | 654 |
1/3/2014 | 3771 | 660 | 12495 | 660 |
1/6/2014 | 3800 | 680 | 12474 | 660 |
1/7/2014 | 3860 | 680 | 12430 | 660 |
1/8/2014 | 3826 | 707 | 12460 | 665 |
1/9/2014 | 3788 | 744 | 12515 | 665 |
1/10/2014 | 3579 | 745 | 12350 | 670 |
1/13/2014 | 3634 | 738.8 | 12574 | 663 |
<tbody>
</tbody>
I am not sure how to use the data in this form, and I assume it is better to have the tickers as IDs (so they can be linked to another table with more ticker data), as below:
Ticker ID | Ticker |
1 | ACL SJ Equity |
2 | BAT SJ Equity |
3 | CSB SJ Equity |
4 | GRF SJ Equity |
<tbody>
</tbody>
Meaning the other table could be as follows:
Date | ACL SJ Equity | Ticker ID |
1/2/2014 | 3764 | 1 |
1/3/2014 | 3771 | 1 |
1/6/2014 | 3800 | 1 |
1/7/2014 | 3860 | 1 |
1/8/2014 | 3826 | 1 |
1/9/2014 | 3788 | 1 |
1/10/2014 | 3579 | 1 |
1/13/2014 | 3634 | 1 |
1/2/2014 | 658 | 2 |
1/3/2014 | 660 | 2 |
1/6/2014 | 680 | 2 |
1/7/2014 | 680 | 2 |
1/8/2014 | 707 | 2 |
1/9/2014 | 744 | 2 |
1/10/2014 | 745 | 2 |
1/13/2014 | 738.8 | 2 |
1/2/2014 | 12315 | 3 |
1/3/2014 | 12495 | 3 |
1/6/2014 | 12474 | 3 |
1/7/2014 | 12430 | 3 |
1/8/2014 | 12460 | 3 |
1/9/2014 | 12515 | 3 |
1/10/2014 | 12350 | 3 |
1/13/2014 | 12574 | 3 |
1/2/2014 | 654 | 4 |
1/3/2014 | 660 | 4 |
1/6/2014 | 660 | 4 |
1/7/2014 | 660 | 4 |
1/8/2014 | 665 | 4 |
1/9/2014 | 665 | 4 |
1/10/2014 | 670 | 4 |
1/13/2014 | 663 | 4 |
<tbody>
</tbody>
Given the size of this matrix, however, I don't want to have to rearrange it manually. Is there a way to rearrange the data such using SQL (or any other means)?
Thanks