This is a post on how to use NETWORKDAYS in an Excel Stock Trading Spreadsheet. Use this workbook to help you track your average profits over your total number of trading days.

In practice, Microsoft Excel is a great tool for tracking your trading gain/loss averages, stock price deviations, and really…it can be used within an endless number of trading scenarios to help you analyze and perfect your strategies.

NETWORKDAYS Function in Excel Stock Trading Spreadsheet

The goal is to track the daily average of total gains or losses over the year, for those days on which you traded. For example, if you’ve traded 120 days so far in the year, and you’ve gained a total of $30,000.00, then your daily average gain is $250.00/year. So, imagine you have a simple tracker, and at the bottom, here’s where you want to show your average:

I’ve started with the basic formula:

=SUM(Gains)/NETWORKDAYS(A2,A440)

“Gains” is the Named Range of the table column “Gain”, and NETWORKDAYS is going to return the total number of weekday days that have passed since the first trading date of the entries you’ve been tracking (stored in A2) through the last specified trading execution you’ve tracked (currently A440).

But, there are two flaws with this default behavior and logic

1. You may not have traded every weekday between the start date and last date you’ve tracked orders for.
2. There may have been market holidays between the first and last trading day you’ve captured.

Together, this set of dates (i.e. your personal days off and market holidays) can be plugged in to NETWORKDAYS as the third argument.

In order to set these dates up so you can reference them in the formula:

• Create a new worksheet named Holidays.
• Add a table titled HolidayTable with a column to store market holiday dates as well as the dates you didn’t day trade.
• Name the HolidayTable, date column, with a Named Range so that it can be referenced globally in the workbook. I’ve used “HolidayDates”.
• Add all of the market holidays, plus the days you didn’t trade to the table.

Note that all of the market holidays are available on the NYSE Holiday Calendar Page.

Now, update the formula to read:

=SUM(Gains)/NETWORKDAYS(A2,A441,HolidayDates)

…and that’s it. The formula now takes the sum of gains and divides by the total number of non working days between the dates you’ve tracked less market holidays and less your days off.

Thanks for reading.

Categories: Business, Microsoft Excel

Tags: Microsoft Excel, Office