27 August, 2021

STOCK PORTFOLIOS Tracking with Yahoo Finance in Excel - Part 2

In Part 1 I've created exemplary Excel Table for your Portfolio of stocks and provided steps you need to follow for updating the table as you'll need from time to time.

Now add a new worksheet in your workbook. Let's call it "Trades".

In this worksheet, based on the raw data in your created Portfolio table you can create Excel Tables for keeping track of your stock trading, buying and selling. 

Let's say, you just started with couple of records in your Yahoo Finance portfolio and created this Excel Table:

Now you'll reference this table and create two tables for keeping track of your trades. Here is an example of such tables, you can follow if you like:

As you can see, I've used cell formatting to differentiate between data entry cells and cells containing formulas. Green cells are for your data entry, all other cells contain formulas. The Gain/Loss % column is conditionally formatted for visual comparison of relative performance of stock positions. I've selected for that purpose Green-Yellow-Red option from Color Scales of Conditional Formatting menu in the ribbon.

To clarify functioning of the "Trades" tables I'm providing their view with all the Formulas shown, so you can see their structure for your specific use:

Obviously, this is just an example. You can edit and modify the tables to your specific needs and liking.

In Part 3 of the "Stock Portfolios Tracking..." I'm presenting a simple approach for summarising the performance of the Yahoo Finance portfolio in Excel, also graphically.

 




No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.