Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    110
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Track Real Time Changes In The Stock Market

    Dear All,

    "With Microsoft Excel and Internet access you can track real time changes in the stock market" we were told on a recent seminar for Financial Analysts.

    The result of this daring statement was that nobody got it to work.

    Some Excel Guru on the team said "The Change Event does not capture everything. I have grappled with this myself on several occasions, sometimes having to resort to putting the new data alongside the old, comparing the two (and taking any action I want if there are changes) then making the new data the old data . . . etc. It may depend mainly by the way you are importing the data".

    I import via Excel's from Web which works fine but does not allow me to do any data tracking like writing the new values to the next cell of the next column.

    Does anybody know of a workaround or something?

    Best regards,


    Wolfgang
    Last edited by wolfgang; 2014-08-14 at 08:09. Reason: Typo...

  2. #2
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 Posts
    Wolfgang,

    Could something like this work for you?

    If you select a cell, its content are immediately saved to a variable called "oldval". Now if you change the cell value, oldval gets put into a comment for the cell. The new value is the cell value while the old value is the comment. You can now compare values by hovering over the cell.

    HTH,
    Maud

    Code:
    Public oldval
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    '------------------------------------------
    'TEST FOR MORE THAN 1 CELL SELECTED
        If Target.Count > 1 Then Exit Sub
    '------------------------------------------
    'COVERT OLDVAL TO TEXT AND PLACE IN COMMENT
        Target.ClearComments
        If oldval <> "" Then
            Target.AddComment
            If IsNumeric(oldval) Then
                Target.Comment.Text Text:=Str(oldval)
            ElseIf IsDate(oldval) Then
                Target.Comment.Text Text:=Str(oldval)
            Else:
                Target.Comment.Text Text:=oldval
            End If
        End If
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    '-----------------------------------------
    'REMEMBER OLD VALUE
        oldval = Target
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    110
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Good Morning Maude,

    thank you very much for your reply!

    When I run your code it barks at the line "Public oldval" and tells me "Invalid Attribute In Sub Or Function".

    Wolfgang

  4. #4
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 Posts
    Wolfgang,

    Please change "Public oldval" to "Dim oldval" without the quotes and try again. Make sure the code is in the worksheet module and not a standard module.

    Place some text or a number in a cell. Come back to the cell and change its value. You will see the old value inserted into a comment for the cell.

    Maud

  5. #5
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    110
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Good Morning Maude,

    sorry for the late reply but I was away for the weekend.

    I implemented your change and the error message is gone.

    The code works when the cell contents is changed manually but not if the change occurs automatically from the internet.

    Therefore, I need to put the data alongside the imported one and compare it.

    Thank you very much for your time and have a nice week.

    Best regards,

    Wolfgang

  6. #6
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 Posts
    Wolfgang,

    Borrowing some code from Zack Barresse that creates a Query_BeforeRefresh event in a class module, prior to the worksheet's auto web update, the modified code from above will take the values in the existing cells and move them into the comments. The web query then updates and the new values are placed in the cells. A comparison between the old values (comments) and the new values (Cell values) can be made.

    Adapting the code to your project is simple:

    1. Create a class module like you would a standard module and paste the following code
    Code:
    Option Explicit
     
    Public WithEvents MyQuery As QueryTable
     
    Private Sub MyQuery_AfterRefresh(ByVal Success As Boolean)
    
    End Sub
     
    Private Sub MyQuery_BeforeRefresh(Cancel As Boolean)
    '---------------------------------
    'DECLARE VARIABLES
    Dim cell As Range
    Dim rng As Range
    '---------------------------------
    'SET rng TO THE RANGE THAT YOU WANT THE VALUES TO MOVE TO COMMENTS
    Set rng = Worksheets("sheet1").Range("A1:A601") 'CHANGE RANGE
    '---------------------------------
    'MOVE CELL VALUES TO COMMENTS
    For Each cell In rng
        cell.ClearComments
        If cell <> "" Then
            cell.AddComment
            If IsNumeric(cell) Then
                cell.Comment.Text Text:=Str(cell.Value)
            ElseIf IsDate(cell) Then
                cell.Comment.Text Text:=Str(cell.Value)
            Else:
                cell.Comment.Text Text:=cell.Value
            End If
        End If
    Next cell
    End Sub
    2. Rename the class module to clsQuery as you would rename a standard modules name (Properties window)

    3. Add to a standard module:
    Code:
    Option Explicit
     
    Dim colQueries As New Collection
     
    Sub InitializeQueries()
     
        Dim clsQ As clsQuery
        Dim WS As Worksheet
        Dim QT As QueryTable
     
        For Each WS In ThisWorkbook.Worksheets
            For Each QT In WS.QueryTables
                Set clsQ = New clsQuery
                Set clsQ.MyQuery = QT
                colQueries.Add clsQ
            Next QT
        Next WS
    End Sub
    4. Add to the ThisWorkbook module:
    Code:
    Option Explicit
     
    Private Sub Workbook_Open()
        Call InitializeQueries
    End Sub
    In my attached workbook, the web query for aol.com updates every minute. Although there are several columns that update, I have selected only those in column A to move values into comments by the code line

    Set rng = Worksheets("sheet1").Range("A1:A601")

    You will need to adjust this line to set the range of values you wish to transfer into comments. If for some reason you interrupt the BeforeRefresh event, you will need to re-run the InitializeQueries routine which was initially ran when the workbook opened.

    Will post workbook shortly from another computer.
    Attached Files Attached Files
    Last edited by Maudibe; 2014-08-18 at 21:06. Reason: added file

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    wolfgang (2014-08-19)

  8. #7
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    110
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Good Morning Maude,

    you made my day!!!

    It works just fine...

    Just one more thing: Is it possible to define the Sheet-range as B2:B22 - D2:D22 - F2:F22 - H2:H22 - J2:J22 - M2:M22 and W2:W22?

    All other ranges in-between show data which are irrelevant.

    Thanks again for your great work.

    Best,

    Wolfgang
    Last edited by RetiredGeek; 2014-08-19 at 05:37. Reason: Added NOPARSE tags

  9. #8
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 Posts
    WG,

    Change the line:

    Code:
    Set rng = Worksheets("sheet1").Range("A1:A601")
    to:

    Code:
    Set rng = Worksheets("sheet1").Range("B2:B22, D2:D22, F2:F22, H2:H22, J2:J2, M2:M22, W2:W22")
    HTH,
    Maud
    Last edited by Maudibe; 2014-08-19 at 16:58.

  10. #9
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    110
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Good Morning Maude,

    All Done...

    Have a nice day and multi thanks.

    Best Regards,

    Wolfgang

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •