Investment Portfolio - Project
Google Finance - Financial Model - Tableau
Embedded Files
Background
One of my hobbies is investing in stocks and ETFs, a passion that started during my studies where I was a member of a student investment committee.
Over the years, I have maintained a small personal investment portfolio on DEGIRO a broker platform to buy/sell securities.
To gain further insights into my portfolio I thought it would be interesting to use Google spreadsheets' built in finance functions and Tableau.
Problem and scope
When opening my broker platform, I was missing a complete picture of my portfolio. I was missing information such as the ability to analyze portfolio value over time, portfolio risk, and security risk.
Also, I wanted to analyze and compare positions in my portfolio to see how each performed relative to each other.
So I was looking for a solution (dashboard) that would help me gain further insights into my portfolio. For this solution I wanted to use real-time data and convert all my positions to EUR.
Data preparation - Google Finance
I started by exploring the Google Finance functions in Google Sheets. These functions allow you to import real-time data on stocks and ETFs from the web.
The first thing I did was to replicate my portfolio in Google Sheets and pull in transaction data from DEGIRO, such as the ticker symbol, purchase prices, purchase dates, and number of shares.
Hereafter I was able to use the ticker (column A) and exchange (column B) to pull in real time data for the security price and exchange rate through Google Finance. With this information I calculated the following metrics in EUR (current value, % daily gain, % total gain)
Data preparation - Financial Model
Going back to my problem statement I wanted to see my portfolio value over time, see my portfolio risk and security risk. I started to think about a solution where I could gather data from Google Finance to analyze these metrics and later build a dashboard around it in Tableau.
So in order to build the financial model I defined the following criteria:
Find the end of month price for each security in the last 3 years.
Convert the end of month price to EUR using the end of month exchange rate.
Calculate the end of month portfolio value for each month also considering newly purchased securities and sold securities.
With this information I could then calculate the portfolio value over time and the portfolio risk using the monthly portfolio returns.
Note: so includes price data for 36 months. I then multiply these prices with the number of shares to calculate portfolio value.
Formula used to calculate end of month security price for each security/month, it considers historical (1. security currency type (EUR or USD), 2. date, 3. security price, 4. exchange rate).
Note: securities with 0 are sold securities
After completing the financial model I created a data table in Google Spreadsheets, which I could use in Tableau to build visualizations.
Exploration - Tableau
I started building my dashboard by thinking about elements that were missing from my broker platform. So I started to create a line graph showing the value of my portfolio over time along with the some key metrics like portfolio value and purchase value. Also I created a pie chart and bar graph showing the distribution of my portfolio among sectors/types.
Then a table shows my holdings ranked by current value and shows the total price change and return as well as a 3 year historical trend line. Next to this table are two more tables showing top performing and bottom performing securities based on total return.
Then I also wanted to compare securities against each other as well as against my total portfolio performance. Through the use of parameters in Tableau you can select a security and all graphs will update based on your selection. Using this feature in Tableau I built a table showing some key metrics such as total return and risk, a double axis line graph showing the price of a security over time, and a dual axis scatter plot showing the risk return performance of the portfolio against securities.
Summary
While my broker platform only gives me a snapshot of my portfolio performance I was looking for a solution where I could also analyze performance over time and risk factors. Through building a financial model in google sheets and with google finance I was able to import real-time financial information on stocks and ETFs. In order to see patterns and gain insights from my portfolio performance I built visualizations using Tableau. Currently I am happy with the final dashboard which I set up in sync with my google spreadsheet so that I can analyze the real time performance of my investment portfolio. For further details on the dashboard or model feel free to contact me.
Page updated
Google Sites
Report abuse