I have two tables I am trying to combine into one pivot table. The first, "Price", has (thousands of) unique symbols which will be my "key".
<tbody>
</tbody>
The second table, "Finra", often has duplicate "Symbol" (stock tickers) with the same date, or other times, only one entry per day.
<tbody>
</tbody>
My goal is to create a Pivot Table and use the information from both tables. For example, I would have all the data, per stock Symbol, plus the SUM of the Short Volume, per day (each day would have its own column, as shown below).
<tbody>
</tbody>
I thought I could simply create a new Pivot Table (and check the box for "Add this data to the Data Model". Then, I assumed Pivot Table would simply sum up the Symbols with matching dates (it works fine from a single table).
Nope. When I go to Data | Relationships | Manage Relationships | New, and attempt to Create Relationship (using Symbol as key), I get a popup that says "Both selected columns contain duplicate values. At least one of the columns selected must contain only unique values to create a relationship between the tables".
I verified that my "Price" worksheet has NO duplicates; however, my "Finra" sheet must have duplicates (no choice). I am clueless what to do.
I | J | K | L | M | |
---|---|---|---|---|---|
1 | Symbol | Close | Low | Volume | |
2 | A | $68.69 | $68.30 | 3,041,728 | |
3 | AA | $42.64 | $41.78 | 3,254,640 | |
4 | AAAP | $81.20 | $81.10 | 280,212 | |
5 | AABA | $72.60 | $72.46 | 6,721,660 |
<tbody>
</tbody>
Price
The second table, "Finra", often has duplicate "Symbol" (stock tickers) with the same date, or other times, only one entry per day.
I | J | K | L | |
---|---|---|---|---|
1 | Symbol | Date | Short Volume | |
2 | A | 10/02/17 | 58,272 | |
3 | A | 10/02/17 | 2,389 | |
4 | AA | 10/02/17 | 502,427 | |
5 | AA | 10/02/17 | 127,118 | |
6 | AAAP | 10/02/17 | 40,018 | |
7 | AAAP | 10/02/17 | 1,959 | |
8 | AABA | 10/02/17 | 145,736 | |
9 | AABA | 10/02/17 | 12,098 | |
10 | AAC | 10/02/17 | 11,600 | |
11 | AAC | 10/02/17 | 17 |
<tbody>
</tbody>
Finra
My goal is to create a Pivot Table and use the information from both tables. For example, I would have all the data, per stock Symbol, plus the SUM of the Short Volume, per day (each day would have its own column, as shown below).
A | B | C | D | E | F | G | H | I | J | K | |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | Sum of daily Short Volume | ||||||||||
2 | Symbol | Close | Low | Volume | Open | High | Median Vol | 10/02/17 | 10/03/17 | 10/04/17 | |
3 | AAPL | 68.69 | 68.30 | 3,041,728 | 69.14 | 69.37 | 1,521,250 | 269,180 | 259,252 | 1,069,711 | |
4 | BTG | 42.64 | 41.78 | 3,254,640 | 41.84 | 42.68 | 3,659,957 | 31,044 | 43,615 | 45,661 | |
5 | MSFT | 81.20 | 81.10 | 280,212 | 81.10 | 81.25 | 387,746 | 90,027 | 205,555 | 427,056 | |
6 | AMD | 72.60 | 72.46 | 6,721,660 | 73.02 | 73.25 | 6,925,884 | 164,879 | 1,180 | 26,698 | |
7 | DKB | 9.04 | 8.97 | 150,371 | 9.03 | 9.26 | 143,532 | 154,845 | 481,874 | 845,546 |
<tbody>
</tbody>
Sheet5
I thought I could simply create a new Pivot Table (and check the box for "Add this data to the Data Model". Then, I assumed Pivot Table would simply sum up the Symbols with matching dates (it works fine from a single table).
Nope. When I go to Data | Relationships | Manage Relationships | New, and attempt to Create Relationship (using Symbol as key), I get a popup that says "Both selected columns contain duplicate values. At least one of the columns selected must contain only unique values to create a relationship between the tables".
I verified that my "Price" worksheet has NO duplicates; however, my "Finra" sheet must have duplicates (no choice). I am clueless what to do.