26 August, 2021

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). 

When finished with editing, right-click within the table and click on Table to Excel - Display inline item in the displayed menu. Next click on To Excel in the new open box. When another small window appears, select Excel(default) in the box beside Open with text. Click OK.

Now Excel file opens and, after a second or so, a warning message will be  displayed. Ignore it and click on Yes button. Excel opens a new worksheet page (with [Protected View] in its name). Click on Enable Editing (if displayed) at the top bar.

With cell selection within the table press CTRL+T to create Excel Table. Format it to your liking using Table Tools > Design in the menu, so it looks e.g. like this one: 

Below the table (or at its side) enter link to the Yahoo Finance web site by copying the web page address, which may look similar to this one:
and pasting it into the worksheet cell. You'll use this link later to update your portfolio table as your portfolio growth (you add new stocks or remove some of them in your Yahoo Portfolio) and as its value fluctuates in time.
Now it's time to save your work.

To update the table at any time, you need to follow these steps:

  • Click on the link created already for the table and find/view your portfolio in the Yahoo Finance site.
  • Right-click within the portfolio table and select Table to Excel..., next click on To Excel.
  • New small window appears.  Select Excel(default) in the box beside Open with text. Click OK.
  • Wait a second and click on Yes button within the displayed warning message band. Excel opens a new worksheet page (with [Protected View] in its name).
  • Select the table data only - without the headers row - and copy the selection. 
  • Go back to your worksheet containing the original formatted table, select cell A2 (in this example) and paste the data with Matching destination formatting option selected.

Your portfolio table has been updated with fresh data. Now delete the page with [Protected View] in its name. Save your workbook.

In Part 2 of the Stock Portfolios Tracking... I'm demonstrating how to make use of the imported raw data for recording and tracking your trades (buys and sales), and evaluating performance of your individual positions.


No comments:

Post a Comment

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