Find the trading date every 2 weeks (if Saturday or Sunday, then show Friday date)

0
votes

For a backtesting trading system, I need to rotation my positions every 2 weeks, BUT if the day is a Saturday or Sunday, I need to take the Friday.

Semi-Monthly updates are made twice a month; mid-month and month end. Mid-month updates are on the 15th calendar day of each month. Should the 15th be a weekend or holiday, the update will occur on the last trading day prior to the 15th. For example, if the 15th is a Saturday then the update will occur on the close of Friday the 14th.

I need to return a list of dates of rotation based on a start date and end date. Let's say, I need every 15 days from January 1st 2018 to 31st December 2018, it should return only the valid dates based on the rules described above.

The formula should be for Google Sheet or Excel.

I tried the following:

http://prntscr.com/nz6j25

http://prntscr.com/nz6j7t

It is not returning exactly what I need, since the google sheet googlefinance formula allows to use daily and weekly intervals (1 or 7). See below the googlefinance definition (https://support.google.com/docs/answer/3093281?hl=en):

"interval - [ OPTIONAL ] - The frequency of returned data; either "DAILY" or "WEEKLY". interval can alternatively be specified as 1 or 7. Other numeric values are disallowed."

1
excelgoogle-sheets

1 Answers

0
votes

You need to be more precise in your specifications. You've provided multiple inconsistent intervals. eg: Two weeks (which would be every 14 days and will always fall on the same weekday); every 15 days (which will NOT be mid-month and EOM over most time periods); mid-month and end-of-month.

I suggest developing formulas for each of your desired intervals.

Once you have developed those relevant formulas, depending on your desired interval, to avoid a date falling on a weekend (or holiday if required for your system), you can use the WORKDAY function:

=WORKDAY(computedDate+1,-1, [holiday])

If your computedDate is on a Saturday, or Sunday; by adding 1 day and then subtracting to the previous workday, Friday will result.

If your trading interval is every Two weeks, you only need to ensure that the First date is not on a Saturday or Sunday. For other intervals, you may have to apply the formula to every computedDate.