SOLVED

Trades Needed in Sheet 2

%3CLINGO-SUB%20id%3D%22lingo-sub-2097092%22%20slang%3D%22en-US%22%3ETrades%20Needed%20in%20Sheet%202%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2097092%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHello%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20attached%20an%20Excel%20file.%20I%20have%20the%20price%2C%20the%20signal%2C%20and%20stop%20loss%20file%20in%20sheet%201.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EIn%20sheet%202%2C%20I%20have%20manually%20filled%20out%20the%20trades.%20I%20need%20functions%20in%20Excel%20which%20would%20fill%20out%20sheet%202%20automatically%2C%20based%20on%20the%20data%20of%20sheet%201.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EIn%20sheet%202%2C%20a%20trade%20is%20initiated%20if%20there%20is%20a%20buy%20signal%20in%20sheet%201.%20There%20is%20also%20a%20stop%20loss%20price%20in%20sheet%201.%20If%20there%20is%20a%20buy%20signal%2C%20then%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E1)%20The%20stop%20loss%20price%20corresponding%20to%20the%20buy%20signal%20(the%20stop%20loss%20price%20in%20Column%20E)%20is%20the%20%22Sell%20Price%22%20(in%20sheet%202)%20unless%20the%20%22Sell%22%20Signal%20(in%20sheet%201)%20is%20reached%20first%2C%20in%20which%20case%20the%20price%20corresponding%20to%20this%20sell%20signal%20in%20sheet%201%20becomes%20the%20%22Sell%20Price%22%20in%20sheet%202.%20Column%20D%20in%20sheet%201%20may%20be%20ignored%2C%20it%20is%20for%20recording%20purposes%20only.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDate%20Initiated%20in%20sheet%202%20is%20always%20the%20day%20the%20security%20was%20bought%20(or%20when%20there%20is%20a%20%22buy%22%20signal)%2C%20but%20there%20are%20two%20possibilities%20for%20%22Date%20Terminated%22.%20If%20the%20stop%20loss%20price%20is%20reached%20first%2C%20then%20the%20date%20terminated%20is%20the%20date%20that%20the%20stop%20loss%20price%20is%20reached%2C%20whereas%20if%20the%20stop%20loss%20price%20is%20not%20reached%20before%20the%20%22Sell%22%20signal%2C%20the%20date%20at%20the%20sell%20signal%20is%20the%20%22Date%20terminated%22%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20will%20highly%20appreciate%20if%20someone%20can%20help%20me%20out%20with%20this.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2097092%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2098903%22%20slang%3D%22en-US%22%3ERe%3A%20Trades%20Needed%20in%20Sheet%202%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2098903%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F931356%22%20target%3D%22_blank%22%3E%40Omer_Ad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehey%20check%20out%20the%20attached%20xl%20file%20which%20i%20made%20it%2C%20used%203%20helper%20columns%20to%20fill%20automatically.%20When%20you%20enter%20%22buy%22%20or%20%22sell%22%20in%20sheet1%2C%20sheet2%20will%20be%20filled%20automatically.%20However%20for%20automation%20i%20strongly%20suggesting%20to%20use%20tables.%20Hope%20you%20will%20understand%20the%20logic%20i%20used.%3CBR%20%2F%3E%3CBR%20%2F%3EHappy%20Trading%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello,

I have attached an Excel file. I have the price, the signal, and stop loss file in sheet 1.

In sheet 2, I have manually filled out the trades. I need functions in Excel which would fill out sheet 2 automatically, based on the data of sheet 1.

In sheet 2, a trade is initiated if there is a buy signal in sheet 1. There is also a stop loss price in sheet 1. If there is a buy signal, then

1) The stop loss price corresponding to the buy signal (the stop loss price in Column E) is the "Sell Price" (in sheet 2) unless the "Sell" Signal (in sheet 1) is reached first, in which case the price corresponding to this sell signal in sheet 1 becomes the "Sell Price" in sheet 2. Column D in sheet 1 may be ignored, it is for recording purposes only. 

 

Date Initiated in sheet 2 is always the day the security was bought (or when there is a "buy" signal), but there are two possibilities for "Date Terminated". If the stop loss price is reached first, then the date terminated is the date that the stop loss price is reached, whereas if the stop loss price is not reached before the "Sell" signal, the date at the sell signal is the "Date terminated"

I will highly appreciate if someone can help me out with this.

1 Reply
Best Response confirmed by Omer_Ad (Occasional Contributor)
Solution

@Omer_Ad 

hey check out the attached xl file which i made it, used 3 helper columns to fill automatically. When you enter "buy" or "sell" in sheet1, sheet2 will be filled automatically. However for automation i strongly suggesting to use tables. Hope you will understand the logic i used.

Happy Trading