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
this will take you most of
this will take you most of the way:
http://excelexperts.com/VBA-Tips-Run-Code-Every-Hour-Minute-or-Second
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.