[Solved] Need help sorting and counting...

Discuss the spreadsheet application

[Solved] Need help sorting and counting...

Postby withnail » Sun Feb 09, 2020 12:46 am

Hi all,
I have a sheet (attached) containing foreign exchange data and need to do some calcs.
Sheet is as follows:

Column A: currency symbol
B: Date
C: Time in minutes (each row represents 1 minute)
D: Opening price
E: High price
F: Low price
G: Closing price

Every day at time 00:00hrs (0 in column C) I place orders at the opening price in D, I'll call this entry price.
I then need to check if at any time during the day the price in E went higher than entry price, and if the price in F went lower than entry price.
The higher or lower prices in E and F specifically need to be 14 ticks higher or lower, so if entry price is 0.8500, then 14 ticks higher would be 0.8514, and 14 ticks lower would be 0.8486.
I don't need to count exactly how much higher or lower the price went, only whether the required 14 ticks has been reached or exceeded.
As I said the entry price is at 0 in column C (00:00hrs), and the order remains open until 234500 in C (23:45hrs), if the high and/or low prices have not exceeded 14 ticks by 23:45 then the order would be closed.
After closure at 23:45 the whole process starts again 00:00hrs the next day.
Some dates are missing because the exchange is closed at weekends.
Also I would need some way of recording/signalling for every situation, i.e., some days both high and low will not exceed 14 ticks, some days both will exceed, and some days high may exceed but not low etc..

I think I can sort out the formula's myself but I don't know how to tackle the problem without making it overly complicated.
I just need a push in the right direction...
Thanks! :-)
Attachments
calc2.ods
(114.22 KiB) Downloaded 23 times
Last edited by MrProgrammer on Tue Feb 18, 2020 5:00 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
OpenOffice 4.1.7
Windows 10
withnail
 
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm

Re: Need help sorting and counting...

Postby MrProgrammer » Sun Feb 09, 2020 4:15 am

withnail wrote:I then need to check if at any time during the day the price in E went higher than entry price, and if the price in F went lower than entry price.
The higher or lower prices in E and F specifically need to be 14 ticks higher or lower, so if entry price is 0.8500, then 14 ticks higher would be 0.8514, and 14 ticks lower would be 0.8486.
Create a pivot table with the date as the row field and Average Open, Minimum Low, and Maximum High as the three data fields. Then you can use IF() functions to check whether the minimum low is 14 points less than the open price and/or the maximum high is 14 points more than the open price. Read about pivot tables in Help → Index or in User Guides (PDF) or searching for topics about them in the Calc Forum.

withnail wrote:B: Date
C: Time in minutes (each row represents 1 minute)
Column B does not contains Calc dates and column C does not contain Calc times, but I don't think it will matter for this analysis. You may want to read section 3. Dates in cells and section 4. Times in cells in Ten concepts that every Calc user should know. You can convert your data to Calc dates and times using [Tutorial] Text to Columns Q08/A08 and Q13/A13.

withnail wrote:Need help sorting and counting...
I can't find any mention of sorting in your post, except your subject line. Your data appears to already be in ordered by date and time. Even if not, the pivot table does not require the data to be sorted. It will display the results in increasing order by date.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3964
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Need help sorting and counting...

Postby withnail » Sun Feb 09, 2020 4:18 pm

Of course, pivot table, that should do what I need.
Thanks, I'll get cracking :-)
OpenOffice 4.1.7
Windows 10
withnail
 
Posts: 69
Joined: Fri Feb 15, 2019 10:57 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 14 guests