Dear All,
While I understand there are a number of posts on returning multiple cells, my question relates to returning multiples returns across rows, and summing the various categories after.
As my sample below demonstrates, I am trying to lookup Trading Dates relating to a company's stock (Column C) against a large selection of Event Dates (Column B) and return any related Event Types (Column A) horizontally across rows beginning in Colum E and beyond.
The problem is that my data analyzes daily trades over the past 20 years. On many of the trading dates there are a number (often identical) event types reported. For example on 2 May 00 the company has 3 news events entitled DIVNEWS. I want ALL three of these to be displayed across columns.
Ultimately, I will be summing across the appropriate event types and utilizing dummy variables to run regressions.
Thank you very much for any advice!
Ian
<table style="border-collapse: collapse; width: 529pt;" border="0" cellpadding="0" cellspacing="0" width="705"><col style="width: 48pt;" width="64"> <col style="width: 55pt;" width="73"> <col style="width: 78pt;" width="104"> <col style="width: 62pt;" width="82"> <col style="width: 50pt;" width="66"> <col style="width: 59pt;" span="4" width="79"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl84" style="height: 15pt; width: 48pt;" height="20" width="64"> </td> <td class="xl84" style="border-left: medium none; width: 55pt;" width="73">A</td> <td class="xl84" style="border-left: medium none; width: 78pt;" width="104">B</td> <td class="xl84" style="border-left: medium none; width: 62pt;" width="82">C</td> <td class="xl84" style="border-left: medium none; width: 50pt;" width="66">D</td> <td class="xl84" style="border-left: medium none; width: 59pt;" width="79">E</td> <td class="xl84" style="border-left: medium none; width: 59pt;" width="79">F</td> <td class="xl84" style="border-left: medium none; width: 59pt;" width="79">G</td> <td class="xl84" style="border-left: medium none; width: 59pt;" width="79">H</td> </tr> <tr style="height: 30pt;" height="40"> <td class="xl84" style="height: 30pt; border-top: medium none; width: 48pt;" height="40" width="64">2</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 55pt;" width="73">Event Type</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 78pt;" width="104">Event Date</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 62pt;" width="82">Trading Date</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 50pt;" width="66">Excess Return</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79">Event 1</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79">Event 2</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79">Event 3</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79">Event n ….</td> </tr> <tr style="height: 30pt;" height="40"> <td class="xl84" style="height: 30pt; border-top: medium none; width: 48pt;" height="40" width="64">3</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 55pt;" width="73">MNGT</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 78pt;" align="right" width="104">10-Aug-09</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 62pt;" align="right" width="82">17-May-91</td> <td class="xl88" style="border-top: medium none; border-left: medium none; width: 50pt;" align="right" width="66">0.00%</td> <td class="xl89" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79">ER</td> <td class="xl89" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79">ER</td> <td class="xl89" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79">MNGT</td> <td class="xl89" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79">DIVNEWS</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl84" style="height: 15.75pt; border-top: medium none; width: 48pt;" height="21" width="64">4</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 55pt;" width="73">ER</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 78pt;" align="right" width="104">10-Aug-09</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 62pt;" align="right" width="82">20-May-91</td> <td class="xl88" style="border-top: medium none; border-left: medium none; width: 50pt;" align="right" width="66">0.01%</td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl84" style="height: 15.75pt; border-top: medium none; width: 48pt;" height="21" width="64">5</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 55pt;" width="73">ER</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 78pt;" align="right" width="104">10-Aug-09</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 62pt;" align="right" width="82">21-May-91</td> <td class="xl88" style="border-top: medium none; border-left: medium none; width: 50pt;" align="right" width="66">-0.14%</td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl84" style="height: 15.75pt; border-top: medium none; width: 48pt;" height="21" width="64">6</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 55pt;" width="73">MNGT</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 78pt;" align="right" width="104">10-Aug-09</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 62pt;" align="right" width="82">22-May-91</td> <td class="xl88" style="border-top: medium none; border-left: medium none; width: 50pt;" align="right" width="66">0.12%</td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> </tr> <tr style="height: 30pt;" height="40"> <td class="xl84" style="height: 30pt; border-top: medium none; width: 48pt;" height="40" width="64">7</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 55pt;" width="73">DIVNEWS</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 78pt;" align="right" width="104">10-Aug-09</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 62pt;" align="right" width="82">23-May-91</td> <td class="xl88" style="border-top: medium none; border-left: medium none; width: 50pt;" align="right" width="66">0.66%</td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> </tr> </tbody></table>
While I understand there are a number of posts on returning multiple cells, my question relates to returning multiples returns across rows, and summing the various categories after.
As my sample below demonstrates, I am trying to lookup Trading Dates relating to a company's stock (Column C) against a large selection of Event Dates (Column B) and return any related Event Types (Column A) horizontally across rows beginning in Colum E and beyond.
The problem is that my data analyzes daily trades over the past 20 years. On many of the trading dates there are a number (often identical) event types reported. For example on 2 May 00 the company has 3 news events entitled DIVNEWS. I want ALL three of these to be displayed across columns.
Ultimately, I will be summing across the appropriate event types and utilizing dummy variables to run regressions.
Thank you very much for any advice!
Ian
<table style="border-collapse: collapse; width: 529pt;" border="0" cellpadding="0" cellspacing="0" width="705"><col style="width: 48pt;" width="64"> <col style="width: 55pt;" width="73"> <col style="width: 78pt;" width="104"> <col style="width: 62pt;" width="82"> <col style="width: 50pt;" width="66"> <col style="width: 59pt;" span="4" width="79"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl84" style="height: 15pt; width: 48pt;" height="20" width="64"> </td> <td class="xl84" style="border-left: medium none; width: 55pt;" width="73">A</td> <td class="xl84" style="border-left: medium none; width: 78pt;" width="104">B</td> <td class="xl84" style="border-left: medium none; width: 62pt;" width="82">C</td> <td class="xl84" style="border-left: medium none; width: 50pt;" width="66">D</td> <td class="xl84" style="border-left: medium none; width: 59pt;" width="79">E</td> <td class="xl84" style="border-left: medium none; width: 59pt;" width="79">F</td> <td class="xl84" style="border-left: medium none; width: 59pt;" width="79">G</td> <td class="xl84" style="border-left: medium none; width: 59pt;" width="79">H</td> </tr> <tr style="height: 30pt;" height="40"> <td class="xl84" style="height: 30pt; border-top: medium none; width: 48pt;" height="40" width="64">2</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 55pt;" width="73">Event Type</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 78pt;" width="104">Event Date</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 62pt;" width="82">Trading Date</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 50pt;" width="66">Excess Return</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79">Event 1</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79">Event 2</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79">Event 3</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79">Event n ….</td> </tr> <tr style="height: 30pt;" height="40"> <td class="xl84" style="height: 30pt; border-top: medium none; width: 48pt;" height="40" width="64">3</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 55pt;" width="73">MNGT</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 78pt;" align="right" width="104">10-Aug-09</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 62pt;" align="right" width="82">17-May-91</td> <td class="xl88" style="border-top: medium none; border-left: medium none; width: 50pt;" align="right" width="66">0.00%</td> <td class="xl89" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79">ER</td> <td class="xl89" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79">ER</td> <td class="xl89" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79">MNGT</td> <td class="xl89" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79">DIVNEWS</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl84" style="height: 15.75pt; border-top: medium none; width: 48pt;" height="21" width="64">4</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 55pt;" width="73">ER</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 78pt;" align="right" width="104">10-Aug-09</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 62pt;" align="right" width="82">20-May-91</td> <td class="xl88" style="border-top: medium none; border-left: medium none; width: 50pt;" align="right" width="66">0.01%</td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl84" style="height: 15.75pt; border-top: medium none; width: 48pt;" height="21" width="64">5</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 55pt;" width="73">ER</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 78pt;" align="right" width="104">10-Aug-09</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 62pt;" align="right" width="82">21-May-91</td> <td class="xl88" style="border-top: medium none; border-left: medium none; width: 50pt;" align="right" width="66">-0.14%</td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl84" style="height: 15.75pt; border-top: medium none; width: 48pt;" height="21" width="64">6</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 55pt;" width="73">MNGT</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 78pt;" align="right" width="104">10-Aug-09</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 62pt;" align="right" width="82">22-May-91</td> <td class="xl88" style="border-top: medium none; border-left: medium none; width: 50pt;" align="right" width="66">0.12%</td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> </tr> <tr style="height: 30pt;" height="40"> <td class="xl84" style="height: 30pt; border-top: medium none; width: 48pt;" height="40" width="64">7</td> <td class="xl86" style="border-top: medium none; border-left: medium none; width: 55pt;" width="73">DIVNEWS</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 78pt;" align="right" width="104">10-Aug-09</td> <td class="xl87" style="border-top: medium none; border-left: medium none; width: 62pt;" align="right" width="82">23-May-91</td> <td class="xl88" style="border-top: medium none; border-left: medium none; width: 50pt;" align="right" width="66">0.66%</td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 59pt;" width="79"> </td> </tr> </tbody></table>
