Create the Excel Spreadsheet Below
I am currently using Excel 2002 but would like it to work if I were to upgrade to a later version.
The projecIt will be a one page spreadsheet with seven columns.
Column A: Format for Date (I will populate with the weekly reference index closing dates)
Column B: Format for Currency or a number with 2 decimal places if necessary to make formulas work. (I will populate with dollar value of reference index on the dates in column A)
Column C: Will display in % (or number if needed to make the formulas work) change in price from the previous week.
Column D will contain the results of the rule below:
A BUY signal will be generated when the dollar value weekly close of the reference index (value in column B) rises four percent or more from the previous weekly close of that index. A SELL/SHORT signal will be generated when the dollar value of the weekly close of the reference index falls four percent or more from the previous weekly close of that index.
SECOND RULE: If the rule generates a BUY signal and the previous signal was a BUY, no signal would be displayed. If the rule generates a SELL signal and the previous signal was a SELL no signal would be displayed.
I started this project with the formula below and found it would work for all but the value -4%. So gave up and am asking an expert!
=IF(C3>=4%,"If out if the market, BUY", IF(C3<=-4%,"If in the market,
Column E: Format for Currency (or a number with 2 decimal places if necessary to make formulas work). I will populate with weekly closing prices of any stock I am interested in, I will call it Alpha.
Column F: Will calculate the % change in the price of Alpha since the last signal.
**Because I will be shorting the market, the value of the % change between the SELL/SHORT signal and the BUY signal is reversed which means it would be positive if the BUY number is lower than the SELL number above and visa versa. Example, if I would sell short the stock at $100.00 and then I Buy it at 75.00 I would have a $25.00 profit because I was betting against the market. The reverse is true.
Column G: Format for Currency. Line one would be the value of an original investment. Each time a signal is generated and we calculate the gain/loss in Column F: this change would be applied the current value in Column G resulting in a running total value of the investment.
21 freelancers are bidding on average $75 for this job
Can do this easily if can discuss detail of columns and where you want the comparison input to go if interested, please contact Mitch at 561-602-0090