Take "snapshot" of a cell value when certain criteria have been met

Hi,

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.

Any help with this would be greatly appreciated!

Hello all, I have made some

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

Nick's picture

this will take you most of

Nick, thx for the reply. Very

Nick, thx for the reply. Very new to VBA so I find it difficult to see how I use this for my situation. My quotes are being updated automatically via Bloomberg Add-in. I need to figure out a way to extract a "snapshot" once the automatically updated Bloomberg quote reached my take profit level to show how much profit I have made. The "snapshot" essentially acts as closing the position and shows the realised profit.