Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Taking a Snapshot from an automatically updated Cell

  1. #1

    Taking a Snapshot from an automatically updated Cell

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello there!

    I am very new to forums, VBA, etc and would greatly appreciate some help with my problem.

    I am piecing together a Profit/Loss Spreadsheet which is automatically updated by the Bloomberg Add-In. I input the price at which I enter a position and then input the price at which I would like to exit the position to take profit.

    I want to be able to say something like "if the live price(updated automatically from bloomberg)> price at which I like to take profit, input the difference at that moment into another cell.
    Basically, this snapshot is like closing the position and recording the profit into another cell.

    Muchos Gracias!

  2. #2
    Hello all,

    I have made some progress on the aforementioned issue but still have a few things to iron out.

    I decided to record a makro which copies the profit as a number into the realised profit column as soon as a certain criteria is fulfilled.

    Function Makrostart() As String
    Call Realized_Profit
    End Function
    Sub Realized_Profit()
    ActiveCell.Offset(0, -1).Range("A1").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    ActiveCell.Offset(0, -7).Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.Offset(0, 6).Range("A1").Select
    End Sub
    The formula behind the cell which should be delivered is as follows:

    =IF(IFEMPTY(D4);"";(IF(D4="Short";IF(C4<=H4;Makrostart();IF(C4>=F4;Makrostart();"")); IF(C4>=H4;Makrostart();IF(C4<=F4;Makrostart();"")))))

    The makro on its own works, however when combined with the formula I worked out in the spreadsheet it always comes up with a #VALUE error when one of the criteria is fulfilled.

    Id really appreciate some help with this. If some further clarification is needed I can prepare a similar sheet that is accessible without bloomberg add-in.

    Last edited by Bob Phillips; 2014-02-22 at 11:22 AM. Reason: Added VBA tags

  3. #3
    Acolyte patel's Avatar
    Join Date
    Feb 2014
    can you attach a sample file ?

  4. #4
    Certainly. A few things to note. The current price column in the original is automatically updated but I have put some sample values in for accessability. The idea is when the current price crosses either the Stop Loss or Take Profit value, the Module "Realized_Profit" should kick in and
    1. copy the unrealized profit value -> realized profit as a hardcode (no underlying equation)
    2. delete the current price and the unrealised profit value

    When running the Makro on its own, the abovementioned steps are completed with no problem but when testing the equation behind Colum J with an adjustment of Current Price into either Take Profit or Stop Loss territory, the #VALUE error comes up in Column J.
    Attached Files Attached Files

  5. #5
    Acolyte patel's Avatar
    Join Date
    Feb 2014
    you used a function without parameters, it does not make sense, you can not use activecell in function.

  6. #6
    PUt this code in the worksheet code module.

    One thing to note, I have coded it to act if the price goes above the stop price or below the take Profit price. Should in be different for Short as against Long positions?

    Private Sub Worksheet_Calculate()Dim cell As Range
        With Me
            For Each cell In .Range(.Range("C4"), .Cells(.Rows.Count, "C").End(xlUp))
                If cell.Value <> "" Then
                    If cell.Value >= .Cells(cell.Row, "F").Value Or cell.Value <= .Cells(cell.Row, "H").Value Then
                        .Cells(cell.Row, "J").Value = .Cells(cell.Row, "I").Value
                        .Cells(cell.Row, "C").ClearContents
                        .Cells(cell.Row, "I").ClearContents
                    End If
                End If
            Next cell
        End With
    End Sub

  7. #7
    Hi Bob and Patel,

    Many thanks for the responses. I cleared the code I had before and replaced it with the one that you gave me. Unfortunately, the error that now pops up when I try to manually change the current price to within stop loss/take profit territory is #NAME in the Realized profit column.

    Also, should the first two lines not read:

    Private Sub Worksheet_Calculate
    Dim cell As Range

    Bob, as for the short and long position, the only difference is the direction from where the current price comes from. So when each position is initiated, this would be the scenario:

    Long Positions
    Take Profit

    Current Price

    Stop Loss

    Short Positions
    Stop Loss

    Current Price

    Take Profit

    In this sense, it would be ideal to write the code so that if the price goes above or below the boundaries set by the take profit and stop loss prices, the makro is triggered.

    I hope this makes sense.

  8. #8
    ARe you saying you get #NAME with my code?

  9. #9
    Yes. WHne replacing my previous code with yours realized profit comes with a #NAME error

  10. #10
    Can't see how that happens, I am not using any formulae or names. Can you post your workbook?

Page 1 of 2 1 2 LastLast

Posting Permissions

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