Data Model for Stock Data and Portfolio Tracking - Need Guidance

[Caz_16]

Hello everyone, 

So I am working on a personal project where I am attempting to build a personal portfolio dashboard in PBI. I have been stuck on getting the columns to relate to each other in order to perform calculations through time, so Ive decided to take a step back and try to rethink my data model and input would be immensely appreciated. 

What I have thus far:

Stock_Main

- Stock ID (Primary Key, Integer)

- Ticker (Ticker Symbol on NYSE, Text)

- Company Name (Name of company, Text)

Price Table w/ variables in columns:

- Date_Stamp (Date of data, Date Format)

- Stock_ID (Foreign key to Stock_Main, Integer)

- Open, High, Low, Close Price (All are separate columns, All Integers).

- Volume (Volume by day, Integer). 

Owed Table

- Purchase ID (Distinct Number assigned to purchase, Integer)

- Stock_ID (FK to Stock_Main, Integer)

- Buy_Date (Date purchased, Date)

- Shares (# of shares purchased on Date, Integer)

- Portfolio (which portfolio this stock is in, Text). 

So because the Price and Owned tables aren't related, I'm having trouble performing calculations on the data such as, multiplying the closing price by the number of shares that I owned at that date. I think the issue lies in the fact that both of these tables are formatted as Data Tables as opposed one of them being a Lookup Table. However, I'm so invested (pun intended) in this that I'm having trouble looking at this from a 30,000 foot perspective and seeing it in a different way. 

My Goal - Is to be able to analyze my portfolio through time based on the number of shares held and the closing price of the stock. Filter down by portfolio, to the individual stocks themselves. I want my portfolio total to be able to adjust based on the purchases and sells of the stocks from my portfolio and see everything adjust accordingly. 

Some input and advice from a different perspective would be greatly appreciated. I will link a Google spreadsheet with some sample data so you can see what I am currently working with. 

Link - https://drive.google.com/file/d/13QOOhy0VCGzt8vK2xoLJDPQ0FgMsr7Pa/view?usp=sharing 

[v-shex-msft]

Hi @Caz_16,

You can add a calculated column to 'Owned' table to lookup value from 'Price' table abed on 'date' and 'stack id':

ClosePrice =
LOOKUPVALUE (
    Price[Close Price],
    Price[Stack Id], Owned[Stock ID],
    Price[Data Stamp], Owned[Purchase_Date]
)

Regards,

Xiaoxin Sheng

[Caz_16]

@v-shex-msft 

Thanks for your reply. That formula helps, but it doesn't really answer my question. I see what it is doing in looking up the price of the stock the day I bought it.

My question really is more about the structure of my data. Is it in a format where I can easily perform calculations between the two tables? I think the answer is no due to the difficulty I'm having in referencing the columns when writing a calc. column or measure. 

I'm trying to understand the PBI best practices and make sure that I'm not cutting corners by writing a ton of formulas to force the software into doing something it wasnt designed to do. I want to have a true comprehension in the way that PBI performs calculations and understand the way the best method for building a data model within the software. 

[v-shex-msft]

Hi @Caz_16,

Can you please explain more about these? It is hard to share helpful formulas without any detail information.

You can provide the data structure, expected result to help us clarify your requirements.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[amitchandak]

At a high level, the structure seems fine.

1. Please have date table. and Join with your Owned Table. Join of date with price depending on use case
2. in the Owned table have a column Amount = Share * cost per Share, prefer column over measure for this

Refer this document

https://docs.microsoft.com/en-us/power-bi/guidance/

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/