Write functions within excel to back test trading scenario based on price data of the S&P 500

Scope of work Using the price data going back to 1950 for the S&P 500 build a set of rules/functions which would allow us to determine if a buy, hold or watch recommendation would be given at the end of the month. If the recommendation requires a trade then what date was the trade made and at what price. Using an excel workbook (which I will provide) I need the following: Within the worksheet titled “Mnth End 12 Mnth Avg 6.5” I need the following returned 1. Column A = Month End Date - Date of last trading day of the month (in Day, Month, Day #, Year format) 2. Column B = Month End Price - Adjusted closing price on the last trading day of the month 3. Column C = 12 Mnth Avg Closing Price – The average of the last 12 months adjusted closing prices 4. Column D = Trigger Price – The price which must be crossed to trigger a trade. A sale trigger price is equal to the 12 Mnth Avg Closing Price multiplied by 0.935. A buy trigger price is equal to the 12 Mnth Avg Closing Price. 5. Column E = Recommendation – Returns a recommendation based on the Month End Price in relation to the 12 Mnth Avg Closing Price in relation to the Trigger Price. Value will be either “Buy/ Hold 100%”, “Sell/Hold 50%” or “Watch Daily” based on the following: a. If monthly closing price < trigger price for the month then “Sell/Hold 50” b. If monthly closing price > monthly average price then “Buy/Hold 100%” c. If monthly closing price < monthly average price but > trigger price for the month then “Watch Daily” 6. Column F = Trade Date – Returns the date which a trade would have taken place if the following condition are met. If the condition is not met then a value of “No Trade” is returned. a. If previous month’s “Recommendation” was “Buy/Hold 100%” and current month’s “Action” is “Buy/Hold 100%” then “No Trade” b. If previous month’s “Recommendation” was “Sell/Hold 50%” and current month’s “Action” is “Sell/Hold 50%” then “No Trade” c. If previous month’s “Recommendation” was “Buy/Hold 100%” and current month’s “Action” is “Sell/Hold 50%” then = the next business day’s “Date” d. If previous month’s “Recommendation” was “Sell/Hold 50%” and current month’s “Action” is “Buy/Hold 100%” then = the next business day’s “Date” e. If current month’s “Recommendation” is “Watch” then search for S&P 500 Daily closing price within the next month date range from the first of the month to the last day of the month for the first closing price < trigger price, If found then = the next business day’s “Date”, if not found then = “No Trade” 7. Column G = Trade Price – Returns the adjusted closing price of the Trade Date a. If Trade Date = “No Trade” then = “No Trade” b. If Trade Date <> “No Trade” then = S&P 500 Closing Price for the next business day