OmniVest Forum OmniVest Forum
forums calendars search
today this week
 
register logon control panel Forum Rules
You are currently browsing as a guest.
You should logon to access more features
A Self-Moderated Community - ALL MEMBERS, PLEASE READ!
Vote for Members who contribute the most to your trading, and help us moderate content within the Forums.


This message cannot be deleted. It is the first message of the thread.
Administrators or moderators may choose to delete the entire thread


 
Mark Holstius

Elite
50010010025
Posts: 744

Joined: 10/11/2012
Location: Sleepy Hollow, IL

User Profile
 
Subject : Trading Day Calculations In Excel
Posted : 4/18/2014 12:25 PM
Post #29924

Now that we have export capability (thanks Ed), it may be more accurate to use trading days in many calculations - and this is somewhat difficult in Excel. I’ve put together a SS with 2 tabs that can be copied into any spreadsheet. You can then modify the formulas to find the nearest trading date “on or before” or “after” a date based on Daily, End Of Week, End Of Month, or End Of Quarter. It can also be used to accurately find the # of trading days between 2 dates.



The data is accurate from 12/31/1999 thru 12/31/2015 and accounts for all holidays.

You just need to “Paste Special Values & Number Formats” all the data from the “Dates” tab starting in cell A1 of a new tab on your spreadsheet also named “Dates”.

You can then copy and paste any of the 9 formulas included on the “Lookup Date Example” tab appropriate to your needs and substitute your date cell references in the formulas as necessary.





Hope this helps with everyone’s research (to all of our benefit…)

Mark


[Edited by Mark Holstius on 4/18/2014 12:30 PM]

Attached file : Trading Days Calculations 2000-2015.xlsx (1060KB - 235 downloads)
Attached file : Dates.jpg (119KB - 413 downloads)
Attached file : EOW Example.jpg (791KB - 395 downloads)
Attached file : Trading Days Between Dates Example.jpg (753KB - 394 downloads)

Deleting message 29924 : Trading Day Calculations In Excel


Nirvana Systems
For any problems or issues please contact our Webmaster at webmaster@nirvsys.com.