Need to return date range for week and month

[AMO315]

I have a trading log that I keep track of my online trades, it has 2 tables on one sheet the primary table is the Trading Log and the secondary table is the Statistics Table. There is a set of cells in the Statistics Table that search the Trading Log to show min and max profits and percentages by day, week and month.

In the Statistics Table I was able to use =MIN and =MAX for the daily min and max for profits and percentages but I would also like to populate the adjacent cells below each result with the date that it took place.
In the Statistics Table cell M4 shows the largest daily profit using this formula =MAX(J3:J2000) the range of cells in column J are in the Trading Log. Below M4 is M5 and I would like the date of what is populated in M4 to be entered in M5 referencing the Cells in column B(Sell Date and Time) of the Trading Log.

Furthermore, I need to find the min and max for a span of a week and month (based on the calendar not just any given 7 day or 30 day range) and populate the adjacent cell with that calendar info i.e 01/01/18 Thru 01/07/18 for weekly profits and percentages and January 2018 or February 2018 formatted as 01/2018 or 02/2018 for the monthly profits and percentages.

As of today I have no working formula to populate the following cells in the Statistics Table N4 (Largest Weekly Profit) and N5 with the date range, O4 (Largest Monthly Profit) and O5 Month and Year, and N7 (Largest Weekly Loss) and N8 with the date range, O7 (Largest Monthly Loss) and O8 month and year, N10 (Highest Weekly %) and N11 date range, O10 (Highest Monthly %) and O11 month and year, N13 (Lowest Weekly %) and N14 date range, O13 (Lowest Monthly %) and O14 month and year.

The dally, weekly, and monthly profit cells in the Statistics Table reference a cell range of J3:J2000 in the Tading Log and the daily, weekly, and monthly percentage cells in the Statistic Table reference a cell range of K3:K2000 in the Trading Log

If you would like a copy of my spreadsheet please advise on how I can get it to you as it is to big to upload here according to the forum.

Any and all help is appreciated

[keme]

• You have made some effort to describe your data structure in detail.
• We need to read that description and translate back to a data structure.
• To test a formula, we would need to build an actual data set conformant to that structure.

All three steps above constitute unnecessary work, and potential sources of error. Please send us actual data instead: A small extract of your file, with confidential content mangled, and perhaps a few comments inside the file. Less work for you, less chance of misunderstandings, and a better chance of getting help. (We are doing this in our spare time, and most of us do not have unlimited time to spare...)

[AMO315]

Sorry I am pretty new to this and do not know how to get you what you are looking for, I can send you the table to look at but I can not upload it here as it is too big.

[robleyd]

Upload to a file sharing service such as Mediafire and post the link to it here.

[keme]

You could also try to cut down on content. 12-15 rows of data from your thousands of rows should be sufficient to give us an idea, and test our ideas. Save as a copy with the rest of your rows deleted. Hopefully that reduces the file size sufficiently to upload.

If that doesn't work for you (e.g. not possible with your data because structure is not entirely tabular), use a file sharing service as robleyd suggested. (Complete file is better to work with anyway, if you are OK with registering on a new service or perhaps are already registered.)

[Lupp]

I wouldn't comment on the statistics about "trading" profits. Concerning my implication a long-term expectancy of -100% is precise enough.

Thus this is exclusively about the subject as put above the thread: "Need to return date range for week and month".

Dates directly usable in calculations are represented by integers (whole numbers) in spreadsheets. There is no standard representation of date ranges, however, except in text. If you want to calculate statistical results with your weeks and months you will therefore need to either use two cells (likely in one row and in adjacent columns) for each single range or to accept complications with formulae referencing the ranges.

The relevant Calc functions for the task are EOMONTH(), WEEKDAY() and TEXT() using format codes for numbers representing dates. You find the basic information about them in the help. As long as you restrict the usage of TEXT() for dates to ISO 8601 extended formats, you can also get back numeric dates from the results in text form by simple means, in specific by using the DATEVALUE() function.

I attach a little demo about how to get the ranges.

[AMO315]

Here is a condensed version of my Trading Log

Trading Log Condensed.ods

(18.05 KiB) Downloaded 66 times