Taking a Snapshot from an automatically updated Cell

[j.hercksen]

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!

 

[j.hercksen]

:eek2: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
Selection.Copy
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
Selection.ClearContents
ActiveCell.Offset(0, 6).Range("A1").Select
Selection.ClearContents
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.

Jannik

 

[patel]

can you attach a sample file ?

 

[j.hercksen]

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.

 

[patel]

you used a function without parameters, it does not make sense, you can not use activecell in function.

 

[Bob Phillips]

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

 

[j.hercksen]

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.

 

[Bob Phillips]

ARe you saying you get #NAME with my code?

 

[j.hercksen]

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

 

[Bob Phillips]

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

 

[j.hercksen]

Hi Bob,

Please see attached. I needed to hardcode Currenct Price and Take Profit as they were both linked with Bloomberg Add In and would have given you an error (unless you also have bloomberg).

 

[j.hercksen]

Hi Bob,

Have you had a chance to look at the sample I included in the last email? Id really appreciate some feedback.

 

[j.hercksen]

Hi all,

Just realized that I had the code in a module and not in the actual code section. It works great for anything with a short position, however it needs to be tinkered with to accomodate long positions as the code is activated as cell value (market) is going to normally be larger than the stop loss in a long position.