How to invest

Written by

Jae Jun

A project that I’ve always had, was to improve on my stock portfolio tracking spreadsheets.

During this time, I’ve probably used 10 or so different portfolio trackers, but nothing met my needs. I don’t do complicated transactions, but still, nothing could really satisfy me.

But those four long years have passed, and I believe I finally have a version that will serve my needs and fulfill its purpose for a long time.

Main Needs from a Portfolio Spreadsheet

• Enter transactions into a single column without splitting up different transactions
• Spreadsheet should be able to automatically update how many shares I’m holding for any company
• Account for dividends
• Account for splits

I don’t do options so I have no need for such transactions. Don’t see why it would be hard for you to edit though.

Previous Versions

The original portfolio spreadsheet could only factor in simple buy and sell transactions and had to separate it.

Then a Google spreadsheet version was released which made it easier to track but not being able to automatically update the cumulative number of shares held for each position, made keeping track difficult.

The improved portfolio spreadsheet even had an interactive time line to track the portfolio growth but it never really took off. Too much hassle of having to update the portfolio values regularly. Also bad to look at your total portfolio that often as well.

Now this new stock portfolio tracking spreadsheet blows it all out of the water. It doesn’t solve everything but it does most of what I need.

New Stock Portfolio Tracker Spreadsheet

Full credit goes to Investment Moats for his amazing spreadsheet. See it in action.

The creator is a Singaporean investor and it will work right away with what he has, but I made some edits to tailor it for the US exchanges (including pink sheets, OTC and ADR’s) and to clean it up a little.

So you have two options. Use the original or use my edited version.

How to Use and Save a Copy

Full details and instructions can be found on Investment Moats’ website. Read the “Read me section” in the spreadsheet as well.

Yellow highlight cells is where you manually enter data. Aqua colored cells are formulas so do not overwrite.

To save a copy into your own account, do the following

• Sign into your Google Docs account
• Open Stock Portfolio Tracking Spreadsheet
• Go to File > Make a Copy
• Rename and press OK to save to your account

How to Edit the Charts

I’ve added three charts to the summary tab. For every new position, you will have to edit the ranges for it to be reflected in the graphs. I will show you how here.

There is a tab called “ChartsData” which holds and sorts the data for the graphs by market value. No need to change anything in the “ChartsData” tab. This tab is only used to display and sort data. Do not enter anything in this section.

Below is one of the graphs/tables in the Summary section.

Click on the table once and a “Chart” menu will appear. Click that and in the menu you can edit the chart.

Select Edit Chart to bring up the chart editor.

The data range is: ChartData!B1:B11, ChartData!D1:D11, ChartData!M1:N11, ChartData!Q1:R11

Columns B, D, M, N, Q and R are used in the table with values from row 1 to 11. In the spreadsheet, I only have 10 holding positions where row 1 is the table headings and the data is contained within row 2 to row 11.

E.g. if you have 20 current holdings and need to update the graph, the data ranges would be

ChartData!B1:B21, ChartData!D1:D21, ChartData!M1:N21, ChartData!Q1:R21

To see the ranges in more detail, click the grid icon next to the range values to bring up this window.

You can add more detail to your table by adding another range.

Press ok and save.

The other two graphs you will need to update include a pie chart displaying sizing and gain/loss.

Another Portfolio Tracker made in Java

Maybe you don’t like the idea of storing your information on the cloud.

Then as an alternative, here is a free, lite, portable and java portfolio tracker that you can use. Again thanks to Investment Moats for bringing it to my attention.

You need to have Java installed on your computer for this to run though. It is still in beta stage so expect bugs and limited documentation but a very good freebie.

Import feature doesn’t work too well so if you have hundreds of transactions to enter, it will take you a long time. But still, better than most.

Truly the Best Stock Portfolio Tracker

After plugging in all my historical transactions, all I can say is that I’m hooked with this version. It has made tracking so much easier. I will continue to add good features when it becomes a need, but until then, enjoy.

Don’t forget to Like, share or tweet it if you like this.

Written by

Jae Jun

You’ve heard and read it over and over again. Management will try to hide information in the footnotes, therefore read the footnotes, read the footnotes, read the footnotes.

But if you are a normal person you don’t like to spend all day, or don’t have the time to spend, reading every fine print in the quarterly and annual reports for each of your companies.

What if I told you there was an easier way?

3 Minutes can Save you 3 Hours

That method is to simply let Microsoft Word compare the documents for you and let you know whether there are any changes.

So simple, it’s brilliant. It’s a lesson from Financial Shenanigans that I’ve been using to make things easier.

This will undoubtedly save you tons of time. After all, you only need to read the differences between each filed report. First see what I’m talking about.

This is just one of the changes that was detected between Dolby’s Q2 and Q3 report. Try to detect this by hand and it will be a nightmare.

Now let me show you how easily this is done.

This tutorial is based on Office 2007, but it also works on word 2000 and 2003.

Step 1: Copy the Reports from SEC

• Go search for the company filings from the SEC
• (In my case, I’m looking at DLB and the 10-Q reports)
• Open the latest Q3 report, select all the text and then copy it. (Press CTRL A or right click and select “select all”)

Step 2: Paste to Microsoft Word

• Open Microsoft Word and then paste in the document. You’ll end up with a document that looks just like the filing.
• Save the document. See the animated image below.

Step 3: Do it Again with the Previous Document

• Now that the Q3 document has been copied, pasted and saved, do it again for the Q2 report.

Step 4: Compare Changes in Microsoft Word

• Now with any of the documents open, select “Review” in the ribbon and then “Compare”.

• When you select compare, a window will open.

• In the original document, select the Q2 document, and in the revised document, select the Q3 document.
• Deselect all the check boxes because you only want to see the word differences. You can choose to check or uncheck “Tables”.
• Press ok. The images below will be what you see.

If you liked this tutorial please share it  by liking or tweeting it. If you have other ideas for tutorials, leave your comment below.

Written by

Jae Jun

Here’s a quick tutorial on how to set up an RSS feed for your company’s insider transactions.

This is something that is extremely quick and easy to set up. Once you get an idea of how this works, you’ll be able to fiddle around with it to search for other specific forms filed to the SEC.

Basics First. All Forms Have Header Information

Whenever a company files their form to the SEC, there is a certain format that must be followed. For example, you can’t just write an annual report and then email it to the SEC.

Each form much comply and contain certain information. That’s where header fields come in. Don’t worry if you don’t know what they are. You just have to know the basics of how to read it.

Below the text between the “< >” is what you call the header field. As you can see below, there are several and you can use each one in your search. Think of it as a filter in your search.

Company Name
CIK
Public Document Count
Accession Number
Form Type
Period
Filing Date

You could use “COMPANY-NAME” to search for a specific company only or include “TYPE” and you can search a specific type of form for a company. This is exactly what I will show you to collect insider transaction filings for a single company.

This is the SEC page for the full list of all header fields you can use to customize your search.

First: Search for Company Name

Go the SEC Archives Search page. This is the page which you want to use for searching.

In the field, enter COMPANY-NAME=”apple” and search.

You will notice that the results display everything containing the word apple. The next step is to clean up the results by using the exact company name as reported in the SEC. In this case, it is “apple inc”. Not case sensitive.

Updating the search term with COMPANY-NAME=”apple inc” now lists filings only for AAPL.

Second: Include Form Type Filter

To narrow your search to include only insider transactions, you will have to search for form 4.

Include FORM-TYPE=4 into your search.

The search term should now be COMPANY-NAME=”apple inc” FORM-TYPE=4

The SEC has kindly added a RSS button so that you can add it to your reader or email. Click and subscribe to it and you’ll be sure to receive it as soon as form 4 is filed.

Advanced Searching Using Boolean Operators

For those of you want a bit more flavor, you can refine your searches even finer by using boolean operators AND and OR.

The easiest way to explain things without going into the jargon is to just see an example first.

COMPANY-NAME=(“apple inc” OR “netflix”) AND FORM-TYPE=(10-Q* OR 10-K*)

In the search string above, I want to find filings for both Apple and Netflix and display both the quarterly and annual reports.

But look, I am not using the AND operator.

The OR operator returns filings for either

1. Apple or Netflix
2. and 10-Q or 10-K.

Use the speech marks ” ” if the company name has a space in it and the * is used to display any filing beginning with 10-Q. By using the asterisk you can search for amended filings that have the code 10-Q/A. If you just did FORM-TYPE=(10-Q OR 10-K), you wouldn’t see any of the amended filings.

For a list of all the other forms, this is the pdf you want.

What’s your customized search string? Share it with us in the comments.

Jae Jun

End of Q1 Value Stock Screen Performances

End of Q2 Value Stock Screen Performances

Results Discussion

A few things stand out here.

• Screens are under performing the market by large amounts
• Current market is not loving value stocks. Growth and momentum stocks are viewed as more attractive.
• All of the cheap stock screen results are being materially affected by Chinese companies that show up in the list. Chinese stocks have been crushed of late as groups such as Muddy Waters and Kerisdale have focused on exposing Chinese frauds, bringing down the entire Chinese space with it.

By looking at the results over the past couple of years, I’m considering replacing the negative enterprise screen with something else, OR possibly add some additional criteria to convert it to a shorting screen.

Compared to the first quarter performance, the Piotroski screen is consistently one of the best performers year over year.

The new low expectations screen is doing exceptionally well by returning 20.6% YTD which is even higher than the results from the backtest. I haven’t been able to upload the screen onto the site yet.

Latest Low Expectation Screen Results

As it is doing so well, here are the latest screen results for the low expectation screen. See if you can find value in the list.

Disclosure: None

Predefined Value Stock Screen Performances

I created these predefined value stock screens in order to keep a constant flow of ideas coming in and to somewhat automate my investment process. The best way to find truly original and off the wall ideas would be to go through every company starting from the A’s in the SEC database, but for  most people, this is unrealistic.

Through these screens, based off my own research and study, I hope to produce ideas that are not producible with just a free standard screener.

The screens are essentially the same as last year, except some parameters have been tweaked and adjusted to account for more realistic trading. A lot of the OTC stocks have been filtered out and I would like to do the same with the Chinese stocks , but that is a hard ask at the moment.

The most obvious point jumping out at me is the negative results of the insider buys and share buyback screen. Something I’ll have to look at and see why.

On the other hand, the CROIC and NNWC increasing stock screener is continuing last year’s outstanding performance where CROIC returned 44.9% and NNWC increasing returned 43.8%.

You can read more about the details of each screen from last year’s performance discussion.

User Interface Changes to Screeners

In case you have not checked out the new site design, the screener section has some changes.

The screens are now being displayed as an embedded spreadsheet so that you can easily copy and paste into your own spreadsheet.

The current portfolio tracking spreadsheet has its shortcomings as it doesn’t include non invested cash when calculating returns.

The true returns of any portfolio will include all cash flows and I have found the XIRR function in excel to be the best to calculate annualized returns.

If calculating returns was as simple as taking the beginning balance and ending balance and then calculating the absolute return, tracking investment returns would be so much easier.

But there is time value in money and once you start depositing or withdrawing cash and receiving dividends, it makes calculating annualized returns that much more difficult.

Investing $1,000 in January certainly is much different to investing $1,000 in December, before the year end.

That’s where the XIRR feature in Excel comes in.

Using XIRR to Calculate Annualized Returns

With XIRR you can calculate annualized returns even when cash flow for your account is irregular.

As an example, the starting balance is $10,000 with regular deposits and some gains totaling a portfolio balance of $15,000 on Jun 27 2010.

At first glance, without taking into account the cash deposits, you could be fooled into thinking the return is 50%. However, if you use the XIRR function, the calculated annualized return is much less at 28.8%.

As you can see, if the cash flows become longer and irregular with different cash flows, the calculation by hand becomes virtually impossible.

Free Spreadsheet to Calculate Annualized and Cumulative Returns

Now you can calculate your returns quickly and easily with this free spreadsheet to calculate your portfolio returns.

Enter your beginning balance at the very top with deposits as positive values and withdrawals as negative values.

Enter the date next to each corresponding cash flow and if you need to calculate the return for any particular date, enter it into the third column.

Just make sure the ending balance is negative with a date to prevent errors.

The results will then look like the image below.

Full credit goes to a site called Gummy Stuff that is unfortunately no longer online for giving me this idea.

Download Portfolio Return XIRR Spreadsheet

• Excel 2007 and newer only.
• No special plug ins required.
• Just enable macros and run.