Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Trading stock data model question

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 i...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
Jimmy801
Community Champion
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Jimmy,

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

    Thanks !!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        jan_tothemoon
        New Member
        4 years ago

        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