Simple Stock Trading Journal with Excel Marco（vba）

April 4, 2021

26776 views

4073 words

Hi guys~

There is a customized excel file with Macro, named “Stock Trading Journal V1.4.4” developed by me, aiming to help you better keep track of the investment on stocks.

This file contains two sheets:

trading and holding. Trading sheet is to input and record information of every transaction you have made while holding sheet is a summary of your stock holdings and investment history.

Trading sheet consists of four parts:

input info
trading details
table
query & outcome and charts

When there is a new transaction, please input the transaction info including: trade date, stock code or name, price, quantity and then click the corresponding action made: Long, Short, Short Buy or Short Sell. All the cells for transaction info are a MUST, i.e. any of the info missing will trigger a error message box “ Pls complete the info”. Trade date is displayed in “mm-dd-yyyy” format.

Every transaction info that is input will be synchronized to trading details table with more info such as, is the buying action a catch up or cover up for the same stock and the average price for it, is the selling action a stop profit (T/P) or stop loss (S/L) and how much profit or loss has been realized. Also you can write down your trading basis and reflections on the transaction.

For frequently- trading investor who wants to know your outcome in a certain period or on a specific stock, the query part can help. Query by date range, input start date and end date separately, and click the green button ”Calculate”. Or query by individual stock, type in the stock code or name and click “Query” button, you will get your no. of profit and loss times as well as profit and loss amount under your query criteria.

Pls be noted, both “Ttl Capital” and “Ttl Realized Profit” are a sum up amount of all stocks which means the two has no relations with your query.

The charts show more vividly and clearly your portfolio, profit and loss and success rate.

There is also “Carry Forward” part, you can input previous record of no. and amount of profit and loss, say, record for the last half year, these previous history will be accumulated to the new trading info, which allows you to have a full picture of investment outcome for a certain period.

Holding sheet summarizes your transactions briefly including total No. of shares on hand, transaction and liquidation date and calculates holding days too. All information on holding sheet is automatic and source data comes from the information on trading sheet. Generally, you don’t need to do anything on holding sheet.

Download

This file is free to download.

←Click HERE.

Welcome to leave your comments, suggestions and advice so that we can make it better for use.

Feel free to leave message to me if there is any bug or you have difficulty opening or using it.

Unzip password:

此处内容需要评论回复后（审核通过）方可阅读。

A password is required to open the file. Password is accessible after you leave your comments .
If it doesn’t work after comments, try to refresh the page please.

Appreciate help from Katrina Xu on this English version file and instructions.

Simple Stock Trading Journal with Excel Marco（vba）