Excel Modification Request

cpatte7372
cpatte7372 used Ask the Experts™
on
Hello Experts,

I haven't asked for Excel help in a while - I hope you Excel gurus can assist me.

Anyway, in the attached spreadsheet an expert helped tweak the existing formula to send provide a sound alert whenever the time paused for 15 seconds or 10 seconds at the following times "04", "09", "14", "19", "24", "29", "34", "39", "44", "49", "54", "59". So for example, the the price paused for 15 seconds at 16:54 or 17:44 or 15:39 or 19:19 etc.... I would get a sound alert (The same applies for pauses at 10 seconds)

Now, that fines. The problem is the spreadsheet is also showing all the pauses outside the specified times of "04", "09", "14", "19", "24", "29", "34", "39", "44", "49", "54", "59". So if you look at the example you will see prices at 16:56, 16:55, 16:57. Can someone show me how I can keep the alerts at the specified times without showing all the other times?

I hope you guys can understand what I'm getting out.

Please let me know if you need further clarification.

Cheers

Carlton
Price-Pause-v5.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi
The whole sheet returns #NAME? since e,g, xlqIntraOpen function is not defined in the sheet you uploaded.

Freeze some of the values so VBA code can run without problems related to the missing functions.
cheers
cslarsen

Author

Commented:
CSLARSEN,

Thanks for responding. Sorry about that. I will freeze and re-upload in two minutes

Cheers

Author

Commented:
Hi CSLARSEN,

As you'll see from the new upload the unwanted numbers are those that don't end with "04", "09", "14", "19", "24", "29", "34", "39", "44", "49", "54", "59", e.g. 18:56, 19:00, 18:55 and so on.

Cheers mate..
Price-Pause-v6.xlsm
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Commented:
Hi
I checked the code in starttimer,
from my point of view the nested IF and Double select  X(1) statements make it difficult to control when a record should be inserted.

e,g, like this
Sub Make_Record(XLOG() As String)
BinPath = ThisWorkbook.Path
BinFile = "ABC.TXT"
BinFileFullpath = BinPath & BinFile

            Open BinFileFullpath For Append As #1  'Change path & name to suit
            Write #1, XLOG
            Close 1
            ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1").End(xlUp).Offset(1, 0).Value = XLOG
           
End Sub

after having determined ONCE whether a record should be created call the sub with the xlog parameter. In short: tidy up the nested if statements and determine ONCE when to dump the record.
Right now it is almost impossible to trace when what happens. at least for me :-)
cheers
cslarsen

Author

Commented:
cslarsen,

Thanks for trying mate. Unfortunately, the formula was coded for me by an expert. I have very limited coding ability. Therefore, I wouldn't know where to begin tidying up the nested statements.....


Cheers

Author

Commented:
Are there any other Experts able to help?

Cheers

Commented:
Hi again,
Could you explain the logic why do you count the number of times the Starttimer runs?
or even better What do you want this sheet to do.
I understand it as at minute intervals ending with "04" or "09" and so on, you want to track with 10 or 15 sec intervals a certain price. Am I correct?
cheers
cslarsen

Commented:
do you want to switch forth and back between 10 and 15secs intervals Why? is it critical?
In your current code Mycount NEVER gets reset to run between 10 and 15and this does not seem right

cheers
cslarsen

Author

Commented:
cslarsen, thanks for sticking with me on on this. Due to the time difference I wasn't able to respond sooner.

The starttimer represents the time the price starts. The start timer begins each time a price changes. For example, the if the price is 11000 at 15:01 the timer will start at 15:01, if the price then changes to 11001 at 15:03, the timer will will start at 15:03.

This is needed because I need to get an alert if the price remains the same for either 15 seconds or 10 seconds.

The reason its critical is because I need to know how long a price remains the same when trading.

Does that make sense?

Cheers

Commented:
ok , so you want a "yellow" alert after 10 secs and then "red" after 15 secs.
So how do you want the alert when it is the same for more than 15 secs?
I understood it as it should alert "red" 4 times if it is the same in within minutes 4-9-14-19......
and nothing in other minutes. Correct?
thx carsten

Commented:
I suggest to check whether the minutes are 4 or 9 before doing anything.
E,g, something like this and then afterwards look into the 10 and 15.
cheers
carsten

Sub Make_Record(XLOG As String)
BinPath = ThisWorkbook.Path
BinFile = "ABC.TXT"
BinFileFullpath = BinPath & BinFile

            Open BinFileFullpath For Append As #1  'Change path & name to suit
            Write #1, XLOG
            Close 1
            ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1").End(xlUp).Offset(1, 0).Value = XLOG
End Sub
Sub StartTimer()
myValue = ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
Dim X() As String
Dim XTIME As String
Dim XLOG As String
DoEvents

If ThisWorkbook.Sheets("mini sized DOW").Range("aw3") = myValue Then
XTIME = Format(Now(), "hh:MM:ss")
    'If Mid(XTIME, 5, 1) = 4 Or Mid(XTIME, 5, 1) = 9 Then
    If Mid(XTIME, 8, 1) = 4 Or Mid(XTIME, 8, 1) = 9 Then
    XLOG = Format(Now(), "hh:MM:ss") & ": Pause " & myCount & " seconds, value' =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
    Make_Record XLOG
    End If
    myCount = myCount + 1
Else
    myCount = 1
    ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = xlNone
    myValue = ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
End If
myTime = Now + TimeValue("00:00:01")
Application.OnTime myTime, "StartTimer"
End Sub

Author

Commented:
CSLARSEN

Thanks again for sticking with me with this. I haven't had a chance to test your formula. Will try it shortly.

BTW, its Green alert after 15 seconds. Will that impact your formula?

Cheers

Author

Commented:
cslarsen,

Do I need replace existing code with your code?

Author

Commented:
cslarsen,

I've pasted in youir code, but nothing appears to happen....

Author

Commented:
Oh cslarsen, even its not working, I think I can see what is meant to happen.

The code is assessing the seconds instead of the the minutes. For example, its looking for 18:51:29 etc. When it should be looking for 18:29:xx. I'm not concerned with the seconds but when a pause occurs at the minute of 04, 09, 19, 24, 29, 34, etc. Whereby your formula is looking when pauses occur at the seconds.

The way you've done is brilliant. If you could just tweak it for minutes and i can test it after.

I would do it myself, but I really don't understand coding very well.

Cheers mate.

Commented:
Hi again
Sorry for the missing change back to minutes.
Enter X(1) instead of X(2) i changed it to faster check whether something would happen at the right time.
cheers
cslarsen
Commented:
Hi again
pasted the wrong section
change in my code :
insert
If Mid(XTIME, 5, 1) = 4 Or Mid(XTIME, 5, 1) = 9 Then
and remove
If Mid(XTIME, 8, 1) = 4 Or Mid(XTIME, 8, 1) = 9 Then

 i changed it to faster check whether something would happen at the right time.
cheers
cslarsen

Author

Commented:
Hi cslarsen,
Thanks for responding. I'm testing now....

Cheers

Author

Commented:
Cslarsen,

This is diamond mate. Absolutely brilliant. Its actually better than I imagined.

Don't know how to thank you.

Cheers mate.

Author

Commented:
The man is a genius.

Cheers

Author

Commented:
cslarson, there appears to be a problem when I insert the code into my live spreadsheet.

When the price changes dynamically, the time doesn't change.

It works if I manually make the change, but as I mentioned when the price changes dynamically it doesn't work.

I wonder if you could help?

Cheers

Commented:
of course and thx for the grade,
would you mind uploading the file as you have it now?
thx carsten

Author

Commented:
Hi cslarsen, thanks for responding. In your absence I managed to get the code to dynamically. However, the sound alert that I got help with constantly alerrts outside the intervals.
dynamic.xlsm

Author

Commented:
The affending code is:

End If
     If myCount = 10 Then
            Call tenminalert(True)
        End If
    myCount = myCount + 1

Commented:
Hi again,
I have checked the trigger of the calltimer event. You use Worksheet_Change.
A formula changing does not trigger the worksheet change event thus no start of the Starttimer occurs.

Maybe Worksheet_calculate or Worksheet_selectionchange will do the trick for you.
cheers
cslarsen

Author

Commented:
Hi,

I did as you mentioned and got an ambigous error message at:

If ThisWorkbook.Sheets("mini sized DOW").Range("aw3") = myValue Then

Author

Commented:
Sorry I meant, 'Application-defined error message

Commented:
Hi again,
It happens because Myvalue is empty, and you can not assign an empty value to a cell.
I would attempt to assign a value to Myvalue first to avoid the problem.
cheers
cslarsen

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial