STOCK PORTFOLIOS Tracking with Yahoo Finance in Excel - Part 3

After completing Excel tables (in Part 2) for tracking trades in your account, it's time for creating some summary of your Portfolio performance. For this purpose you can create another Excel Table (e.g. called "Portfolio Status") and a chart based on the table content. This will allow you to conduct a quick and easy visual evaluation of your investment positions.

Start with adding a new worksheet named e.g. "Performance". First, create the table. Here is an exemplary format and content:

As in previous tables the cells filled with green colour indicate data entry cells. The remaining cells contain formulas filled automatically after entering all necessary data. Cells in column E are conditionally formatted, so that cells showing gains are filled with yellow background.

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:

STOCK PORTFOLIOS Tracking with Yahoo Finance in Excel - Part 1

Do you want to manage your stock portfolio in Yahoo Finance and follow its performance in your private Excel workbook rather than somewhere in a cloud? If so, I'll show here, step by step, how to do it.

I assume that you are actual user of the Yahoo Finance site. If not, then sign up there with user Id. and password.

Create your own portfolio, if you don't have any yet, by clicking on Create Portfolio in the menu, entering portfolio name (e.g. "BestView") and selecting the currency. Click on Submit button.

At this stage you can select either one of the available Views, e.g. "DayWatch", or use Create New View option to make your own selection of portfolio columns you want to include in your View. Having done the selections, now enter your stock symbols you need to follow.

When you finish, your portfolio could look like e.g. this in the "BestView" (View using your own selections of columns: 

If you change your mind, you can still edit the table after clicking on Edit View). 

TIPS for CLICKS: Working with Excel Tables

Excel Tables are one of those basic, most useful features used for data recording and analysis. Excel makes its tables smarter, more versatile, cognitive, and easy to use all the time.

However, if you spend lots of your time working with Excel tables it's worth to remember all keyboard and mouse shortcuts available for tables. They help to use the tables more efficiently and save some time. Here is my collection of the most frequently used shortcuts:

 

How to create MUTUALLY DEPENDENT CELLS

Mutual dependence of Excel cells means that some two cells in your workbook refer to each other. If the value of one of the two dependent cells changes it causes change of value in the other cell dependent on it. This normally leads to a problem called "circular reference". However, it is possible to set up mutually dependent cells by following steps shown in my example below.

TIPS for CLICKS: Custom Lists

Using Excel Custom Lists

What are Custom Lists used for? They are used mainly for SORTING or FILLING any regular Excel lists and tables in a specific user-defined order. Using the lists may save you a lot of time when frequent sorting or filling your lists is needed when working with Excel data.

Excel provides couple of built-in lists by default, as you can see below: 

 

How you can use them? Here is the way (if you haven't used them never before):

• in your selected cell type in any entry from the custom list, e.g. Jan, or Mon, etc.
  
• click on the small box at the bottom right corner and drag it any way you want to - down, up, right, left - to populate any number of cells, in rows or columns, with the list items.

There are also many "hidden" custom-like lists you can built and use easily as needed. Below are some examples of such lists that Excel accepts and readily auto fills for you:

Custom Excel Worksheet / Workbook Design

Have a specific problem in using Excel?

Maybe I could help...

The tools Excel provides for its users allow creating the abundance of solutions for entering, analysing, presenting and storing of datasets. However, this usually requires more or less in-depth knowledge of the software. Sometimes you may need to create a specific solution to a complex computational task, but your experience isn't sufficient to do that. In such situations you may need to look for help. I could be able to provide some no-obligation advice or assistance in solving your problems related to organizing your data and analysing them according to your needs. You could probably save some time, improve your design, etc..

Define your specific problem or your custom design needs and let me know about them using the Contact box located at the right side of this page. I may be able to provide some assistance in design details and analysis of data or actually design a spreadsheet for you.

Here are just couple of examples, fragments of worksheet designs, I've created some time ago for my own needs.