Trading stock data model question

[Anonymous]

Hey there,

I'm managing a small stock portfolio in Excel. I had a blast doing a lot of analysis using Power Query, but recently i've discovered an issue that made me think the data model i'm using is not ideal. What i did recently was to buy a stock i already had previously. Here is how it works : I'm retrieving from Yahoo values using the following great guide (sorry in French) here.

so the result is a table like this (i'm using only a few days and only 3 stocks for demonstration purpose) :

|  code | Date       | Open   | High   | Low    | Close  | Adj Close | Volume  |
|------:|------------|--------|--------|--------|--------|-----------|---------|
| AB.PA | 01/03/2021 | 15,74  | 16,64  | 15,72  | 16,6   | 16,6      | 255598  |
| AC.PA | 01/03/2021 | 34,92  | 35,33  | 34,63  | 34,81  | 34,81     | 1150910 |
| AI.PA | 01/03/2021 | 125,75 | 127,4  | 125,65 | 127,4  | 127,4     | 1250952 |
| AB.PA | 26/02/2021 | 15,36  | 15,84  | 15,1   | 15,72  | 15,72     | 165965  |
| AC.PA | 26/02/2021 | 34,11  | 35,07  | 33,61  | 34,56  | 34,56     | 1251602 |
| AI.PA | 26/02/2021 | 126    | 127,15 | 124,25 | 124,7  | 124,7     | 1813739 |
| AB.PA | 25/02/2021 | 15,8   | 15,88  | 15,4   | 15,56  | 15,56     | 141766  |
| AC.PA | 25/02/2021 | 35,41  | 35,49  | 34,67  | 34,75  | 34,75     | 1121085 |
| AI.PA | 25/02/2021 | 129,45 | 129,5  | 126,8  | 126,8  | 126,8     | 1554359 |
| AB.PA | 24/02/2021 | 15,9   | 16     | 15,36  | 15,62  | 15,62     | 243284  |
| AC.PA | 24/02/2021 | 34,07  | 35,62  | 33,63  | 35,11  | 35,11     | 1338579 |
| AI.PA | 24/02/2021 | 130,15 | 131,75 | 129,3  | 129,3  | 129,3     | 1022464 |
| AB.PA | 23/02/2021 | 15,52  | 16,08  | 15,08  | 15,7   | 15,7      | 495847  |
| AC.PA | 23/02/2021 | 33,5   | 34,52  | 33,31  | 34,18  | 34,18     | 1572645 |
| AI.PA | 23/02/2021 | 131,3  | 131,5  | 129,35 | 130,35 | 130,35    | 952000  |
| AB.PA | 22/02/2021 | 15,3   | 15,44  | 15,08  | 15,24  | 15,24     | 89498   |
| AC.PA | 22/02/2021 | 31,34  | 33,26  | 31,19  | 33,26  | 33,26     | 1235785 |
| AI.PA | 22/02/2021 | 132,1  | 132,2  | 130,85 | 131,1  | 131,1     | 835870  |

I also have an excel tab (the transaction table) in which i keep track of the transactions with the code, the acquisition date & price, the quantity, disposition date & price (if relevant) etc ...

These 2 tables are part of the data model and related through the code of the stock.

Then In power pivot, i'm using the yahoo query and i add several calculated columns for things like margin, quantity (using the related() function) etc ... This way i can keep track of daily things like the daily margin and the daily value. This daily value by example, allows me to simply produce a graph with the evolution of the value (for each stock or the total) on a daily basis. This is great.

Of course, my issue is that if in my transaction table i'm adding a stock that already exist previously (i decide by example to buy a stock that i sold 2 months ago), the simple action to bring the quantity using the related function doesnt work because then i have 2 references of the same value in my transaction table.

I've been scratching my head for several days as to the approach i should take here. i should keep the yahoo query as reference to daily prices but then, how can i build the daily values/margin etc ... for each of the values i have in my transaction table ?

Using Excel 365.

i'm including an example file in which, if you refresh, an error message will pop up because i have a duplicate in the transaction table (if i remove the duplicate, the refresh will work fine) :

https://drive.google.com/file/d/1IxwCh82fCCX4NCp7FogzeMnKWiscPdms/view?usp=sharing 

While i fully understand why i have this error, i'm wondering what is the solution to allow duplicates in the transaction table.

thanks

[Jimmy801]

Hello @Anonymous 

you can try to create new table with only the code-column using the column of your transaction-table (or also maybe add the column from your yahoo-query) and get only distinct values. Use this new table as dimension in your datamodel and connect them to both of your facttables (Transaction and yahoo). Maybe some measure will need to be updated but it should work

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Thank you Jimmy !

Quick question : to be able to add columns to the yahoo query (the dailies table) i'll need to have as many stocks in the yahoo query than the transaction table right ? (in the file example, i have 4 transactions and 3 stocks). In that case, how can i synchronize the list of stock in the transaction table (col B) with the query "list valeurs" (which i'm updating manually everytime i have a new stock to fetch ?

Again, thanks.

[Jimmy801]

Hello @Anonymous 

we are talking here about one column called "code" right? You can create a new table using such a query, that takes the code-column of both of your queries, combines them and get distinct values.

Table.Distinct(Table.Combine({Table.SelectColumns(YourYahooQuery, "code"), Table.SelectColumns(YourTAQuery, "code")}), {"code"})

You can use this table then for connecting your both other queries.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hi Jimmy,

I understand now what you're suggesting, let me try tonight and get back to you 🙂

Thanks !!

[Anonymous]

Jimmy, i did differently actually. I had to redo almost everything and restart from scratch which allowed to clean a lot of things. 

What i did was to create an index in both tables and set the relationship on it instead of the stock name (pretty basic when i look back at it).

Thanks for the help anyway !

[jan_tothemoon]

Hi Etip, 

I´m trying to build something similar in excel but I'm a extremely beginner in Power query.. think you could share your file to grap an idea for my report? 

I´d really appreciate it