[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

Excel Modification Request

Hello Experts,

An expert kindly modified the attached spreadsheet to point to a text file to log events. I would like the same information to be shown in the present please.

So the following logs an event to a file called ABC.txt

Sub StartTimer()
   DoEvents
   If ThisWorkbook.Sheets("mini sized DOW").Range("aw3") = myValue Then
      If myCount > 15 Then
        'set lastAlert =2
        If lastAlert <> 2 Then
           
           
            lastAlert = 2
        End If
            DoEvents
      ElseIf myCount = 15 Then
      Call tenminalert(True)
      Call shiftDown
            '-- green highlite when over 15 seconds
            ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = 4
            Open "C:\Users\User\Documents\ABC.txt" For Append As #1  'Change path & name to suit
            Write #1, Format(Now, "dd-mm-yyyy hh:MM:ss.ss") & " Highlight 15 seconds, value =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
            Close 1
      ElseIf myCount > 10 Then
        'set lastAlert =1
        If lastAlert <> 1 Then
           
            lastAlert = 1
        End If
              DoEvents
      ElseIf myCount = 10 Then
      Call fiveminalert(True)
            Call shiftDown
            '-- yellow highlite when over 10 seconds
            ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = 6
            Open "C:\Users\User\Documents\ABC.txt" For Append As #1  'Change path & name to suit
            Write #1, Format(Now, "dd-mm-yyyy hh:MM:ss.ss") & " Highlight 10 seconds, value =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
            Close 1
      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

I happy with it to continue logging to the file I would just like the same information shown on the actual spreadsheet.

Cheers

Carlton
EE-ExcelLogging.xlsm
0
cpatte7372
Asked:
cpatte7372
  • 17
  • 14
1 Solution
 
redmondbCommented:
Hi.

You'll need to add a sheet call "Log". I've just added two lines, please see the following...
Sub StartTimer()
   DoEvents
   If ThisWorkbook.Sheets("mini sized DOW").Range("aw3") = myValue Then
      If myCount > 15 Then
        'set lastAlert =2
        If lastAlert <> 2 Then
            
            
            lastAlert = 2
        End If
            DoEvents
      ElseIf myCount = 15 Then
      Call tenminalert(True)
      Call shiftDown
            '-- green highlite when over 15 seconds
            ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = 4
            Open "C:\Users\User\Documents\ABC.txt" For Append As #1  'Change path & name to suit
            Write #1, Format(Now, "dd-mm-yyyy hh:MM:ss.ss") & " Highlight 15 seconds, value =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
            Close 1
            Sheets("Log").Cells(Sheets("Log").Range("A1").SpecialCells(xlCellTypeLastCell).Row + 1, 1) = Format(Now, "dd-mm-yyyy hh:MM:ss.ss") & " Highlight 15 seconds, value =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
      ElseIf myCount > 10 Then
        'set lastAlert =1
        If lastAlert <> 1 Then
            
            lastAlert = 1
        End If
              DoEvents
      ElseIf myCount = 10 Then
      Call fiveminalert(True)
            Call shiftDown
            '-- yellow highlite when over 10 seconds
            ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = 6
            Open "C:\Users\User\Documents\ABC.txt" For Append As #1  'Change path & name to suit
            Write #1, Format(Now, "dd-mm-yyyy hh:MM:ss.ss") & " Highlight 10 seconds, value =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
            Close 1
            Sheets("Log").Cells(Sheets("Log").Range("A1").SpecialCells(xlCellTypeLastCell).Row + 1, 1) = Format(Now, "dd-mm-yyyy hh:MM:ss.ss") & " Highlight 10 seconds, value =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
      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

Open in new window

You also are adding entries in Worksheet_Calculate(). Please let me know if you want that changed as well and, if so, the format - one cell or four.

Regards,
Brian.
0
 
redmondbCommented:
Oh, every so often, the second will change between writing the text file and the cell. If that's important, please let me know - it's just a matter of storing the time in a variable and writing that to both places.

(BTW, the decimals in your time is simply the second repeated.)

Regards,
Brian.  
0
 
cpatte7372Author Commented:
Hi Brian,

Thanks for taking a look at this. I will test it out when the markets open and let you know.

Cheers mate.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
cpatte7372Author Commented:
Hi Brian,

There just a couple of things.

a) Would it be possible to have log on the same sheet as the main sheet "mini sized dow"? If the log started at AY5 that would be great.

b) As I'm using spreadsheet to trade on the stock market, where every second counts, the delay in writing the text file and cell as you mentioned would mean a lot. Therefore, if storing the time in a variable and writing that to both places would resolve this issue, that would be great.

Cheers mate.
0
 
redmondbCommented:
cpatte7372,

(A) There are a lot of apparently unused rows in that sheet. Could you tidy up the sheet by removing them, please - that'll make everything faster and there'll less chance of overwriting something.

(B) No problem, Please note that the issue of the time isn't around speed, just consistency between the second in the sheet and in the text file.

BTW, I noticed that in the hidden columns there's at least one row where a cell's formula has been replaced by a value. You might want to check that the formulas are consistent "all the way down".

Regards,
Brian.
0
 
cpatte7372Author Commented:
Hi Brian,

Will do it now. Give me a sec.
0
 
cpatte7372Author Commented:
Hi Brian,

I not sure what rows you're referring to? I was thinking you could start the logging from row 5, at AY5. I don't think that would impede anything.

Thanks for making me aware of the cell formula replaced by a value. I will check that out now.

Cheers

Carlton
0
 
cpatte7372Author Commented:
I found the cell replaced by a value.

Cheers mate.
0
 
redmondbCommented:
cpatte7372,

Excel thinks that the last used cell on that sheet is BR8373. To avoid the logging potentially blasting through some other data, please tell me what the last cell should be.

Thanks,
Brian
0
 
cpatte7372Author Commented:
Hi Brian,

I think the cell may exist because in the past I had a formula that shifted cells down on, when an event occurred. Other than that the cell shouldn't refer to anything.

Cheers


Carlton
0
 
redmondbCommented:
Carlton,

Thanks, so what is the real last used cell?

Thanks,
Brian.
0
 
cpatte7372Author Commented:
The real last cell used I guess would be AK34

Cheers
0
 
redmondbCommented:
Carlton,

Please see revised code below. Few points...
(1) BEFORE adding the code, please put something in AY4 so your first log entry will be in AY5.
(2) As discussed, the identical log entry will be in both the cell and the log file.
(3) Becaue I don't have your realtime add-in, I can't properly check my code. Whenever your events ran they gave errors which I just cancelled, so please make sure any open files are saved, your seat-belt is fastened, etc. before applying this update.

Regards,
Brian.

 
Sub StartTimer()
Dim xLog As String
   DoEvents
   If ThisWorkbook.Sheets("mini sized DOW").Range("aw3") = myValue Then
      If myCount > 15 Then
        'set lastAlert =2
        If lastAlert <> 2 Then
            
            
            lastAlert = 2
        End If
            DoEvents
      ElseIf myCount = 15 Then
      Call tenminalert(True)
      Call shiftDown
            '-- green highlite when over 15 seconds
            ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = 4
            xLog = Format(Now, "dd-mm-yyyy hh:MM:ss.ss") & " Highlight 15 seconds, value =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
            Open "C:\Users\User\Documents\ABC.txt" For Append As #1  'Change path & name to suit
            Write #1, xLog
            Close 1
            ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1048576").End(xlUp).Offset(1, 0).Value = xLog
      ElseIf myCount > 10 Then
        'set lastAlert =1
        If lastAlert <> 1 Then
            
            lastAlert = 1
        End If
              DoEvents
      ElseIf myCount = 10 Then
      Call fiveminalert(True)
            Call shiftDown
            '-- yellow highlite when over 10 seconds
            ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = 6
            xLog = Format(Now, "dd-mm-yyyy hh:MM:ss.ss") & " Highlight 10 seconds, value =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
            Open "C:\Users\User\Documents\ABC.txt" For Append As #1  'Change path & name to suit
            Write #1, xLog
            Close 1
            ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1048576").End(xlUp).Offset(1, 0).Value = 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

Open in new window

0
 
redmondbCommented:
Oh, and if you haven't deleted the rows below 34 and there's any data in column AY then the log will start below that.

And I didn't check for the end of the spreadsheet, so a million-plus log entries might cause a problem. :)
0
 
cpatte7372Author Commented:
Ha ha, thanks Brian.

I won't trade live with the spreadsheet before running some extensive tests with it.

I will be testing it out over the next two hours before resuming trading at 2pm EDT.

Know doubt I willl be replying with an additonal request to the spreadsheet in 20mins :-)

In the meantime, thanks ever-so-much mate.

Carlton
0
 
redmondbCommented:
All the best!
0
 
cpatte7372Author Commented:
Hi Stephen,

Its looking good so far. I was just wondering if its possible to have the actual results start logging at row 5 col AY, ie AY5?

I was wondering if I needed to change the following in order to make that happen:

ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1048576").End(xlUp).Offset(1, 0).Value = xLog

Is it the 'Offset' that determines where the actual data is inserted?

I have one other last request, but one at a time :-)

Cheers
0
 
redmondbCommented:
Carlton,

Where did it start? Did you put something in AY4 and delete the rows after 34?
 
Regards,
Brian.
0
 
cpatte7372Author Commented:
Stephen,

Scratch the last request its fine where it is.

However, as I mentioned I do have one last request - and its where all this gels together.

Can you please, please help modify what the code you've written to include an alarm and a highlight when the following numbers are found in the log,

:04:
:09:
:14:

This goes on but if you show me how to do for the above and should be able to take it from there.

So, for example, lets say the following have been logged on the spreadsheet as you've helped me with:

"26-10-2011 15:20:39.39 Highlight 10 seconds, value =8525"
"26-10-2011 15:20:44.44 Highlight 15 seconds, value =1140"
"26-10-2011 15:25:09.09 Highlight 10 seconds, value =8750"
"26-10-2011 15:25:15.15 Highlight 15 seconds, value =8596"

Now I would dearly like an alert if any of the following occurred instead of the above:

"26-10-2011 15:04:39.39 Highlight 10 seconds, value =8525"
"26-10-2011 15:09:44.44 Highlight 15 seconds, value =1140"
"26-10-2011 15:14:09.09 Highlight 10 seconds, value =8750"
"26-10-2011 15:19:15.15 Highlight 15 seconds, value =8596"

Notice :04:
:09:
:14:
:19:

Just by way of explanation, it is those times of the hour that I make money.

Cheers
0
 
redmondbCommented:
Carlton,

It's Brian here. I assume the last comment is a separate question?!

Regards,
Brian.
0
 
cpatte7372Author Commented:
Hey Brian,

How you doing mate? Hope you had a nice time on the East Coast.

Yes, I was going to open another question to solve that issue.

Cheers
0
 
redmondbCommented:
Carlton,

First "Stephen", now "East Coast"???

Step away from the computer. Do not make any deals. :)

Regards,
Brian.
0
 
cpatte7372Author Commented:
Ha ha,

I'm losing the plot.... My brain is freezing. I'm trading while at the same time trying to compile a spreadsheet.

I think you're right, I need to step away.

In the meantime, shall assign the points and open a new question to solve my latest issue?

P.S.
Sorry about the confusion mate.
0
 
cpatte7372Author Commented:
Brian, I don't why I called you Stephen?? :-)

Anyway, did I make myself clear with the last request about :04:, :09:, etc?
0
 
redmondbCommented:
Carlton,

I think you're right, leave this for tonight and concentrate on the day job. Let me know when you're back.

Regards,
Brian.
0
 
cpatte7372Author Commented:
I'm going to take your advice mate.... my brain has been drained.

Lets catch up tomorrow.

In the meantime, thank you very much.


Cheers
0
 
cpatte7372Author Commented:
Hello Brian AKA Stephen (just joking)

I'm a little less brain dead than I was yesterday.

I'm going to close this ticket and make a new request. I hope you'll continue to assist me.

Cheers mate.
0
 
cpatte7372Author Commented:
Brian has been absolutely tremendous....
0
 
redmondbCommented:
Many thanks, Carlton. I see your new question is being dealt with, but I'll keep an I out.
0
 
redmondbCommented:
"I out" - where did that come from?! Carlton, please don't tell me we're dealing with a contagion here!
0
 
cpatte7372Author Commented:
"I out", that made me laugh :-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 17
  • 14
Tackle projects and never again get stuck behind a technical roadblock.
Join Now