Using COUNTIFS formulas when linking worksheets
excel,excel-formula,excel-2013,forex
You need to include the sheet name in all the ranges, i.e. =COUNTIFS(Input!H11:H61,"YES",Input!Q11:Q61,"Win")...
Python/Pandas how to store forex tick data for analysis
I used to play with some cash equity tick-by-tick data (top 30% liquid stocks, over 5 mil records per day). Here is my strategy to deal with file-reading issue using chunksize and hdf5. import pandas as pd # this is your FX file path file_path = '/home/Jian/Downloads/EURUSD-2015-05.csv' # read into...
How can I create an average length of time in from multiple dates / times?
As you say, it's difficult to calculate the average from column O because those are text strings, you can simply use the average of the entry timestamps subtracted from the average of the exit timestamps, e.g. =AVERAGE(M10:M20)-AVERAGE(B10:B20) or to get formatted as per the column O format =INT(AVERAGE(M10:M20)-AVERAGE(B10:B20))&" Days "&TEXT(AVERAGE(M10:M20)-AVERAGE(B10:B20),"h""...
Combining multiple IF/AND/THEN statements
Maybe something like: =IF(OR(AND(D11="long",M11>=N11),AND(D11="short",N11>=M11)),"Win","Loss") ...
Trying to use countif with multiple criteria involving a time range and a set amount [duplicate]
excel,excel-formula,excel-2013,forex
As guitarthrower suggested in the comments, you could use the COUNTIFS function to achieve what you are looking for: WINS =COUNTIFS(E11:E61,">=2:00:00",E11:E61,"<=6:59:59",Q11:Q61, "WIN") LOSSES =COUNTIFS(E11:E61,">=2:00:00",E11:E61,"<=6:59:59",Q11:Q61, "LOSS")...
Using conditional formatting with multiple conditions to color format cells
excel,excel-formula,excel-2013,forex
I was able to solve this problem by following the below steps: Home>Style>Conditional Formatting Create New Rule - "Use a formula to determine which cells to format" I had to create 5 different rules due to my session time setup. Each rule followed the basic setup: =AND($e11>=TIME(7,0,0),$e11<=TIME(9,59,59)) I only had...
Python/Pandas resampling Forex tick data for tick volume
I've got a copy of part of your sample FX data (USD/EUR in May 2015) in hdf5, So I will use it here for illustration purpose. import pandas as pd Jian_h5 = '/media/Primary Disk/Jian_Python_Data_Storage.h5' h5_file = pd.HDFStore(Jian_h5) fx_df = h5_file['fx_tick_data'] # I've only got USD/EUR in this dataset, but let's...
Proper use of AND and OR within IF statements
excel,excel-formula,excel-2013,forex
AND, OR and IF are all functions not just statements. You have 2 sets of conditions which are valid so put the ANDs inside the OR: =IF(OR(AND(D11 = "Short", G11 > 0), AND(D11 = "Long", G11 < 0)), "YES", "NO") ...
How can I use imbedded IF statements to include AND/OR to properly solve multiple conditions?
excel-formula,excel-2010,forex
Here is a solution. You need to put it into one single line: =IF(AND(P10="Win"; D10="Long"); ABS(N10-C10)*10000; IF(AND(P10="Win"; D10="Short"); (c10-n10)*10000; IF(AND(P10="Loss"; D10="Long"); (n10-c10)*10000; IF(AND(P10="Loss"; D10="Short"); (c10-n10)*10000; ) ) ) ) ...