Peter, This spreadsheet I use is a "work-in-progress" (g) As I go along - I modify it.
Would your spreadsheet show (by security) the current outstanding position,
Yes I have a column off to the far right with a "o" for open that I change to a "c" for closed once it is sold. I sort by that column followed by a sort by ticker, so each morning I have all open positions together. I than add new purchases that day.
---------------------------------------
accumulated capital gains in that security on closed positions and unrealized gain/loss on open positions - regardless of the account in use, and complete detail in determining such figures.
I basically have all the above. Using the "c" and "o" flags, and using the broker flags ("m" for MBTraders for example), THEN far off to the right I have two columns ("sale/current value" and "cost") for each broker under the wide heading: "ALL TRADES", and I repeat this all under "OPEN TRADES" and again under "CLOSED TRADES"
Here the fully automatic formulas may seem tricky at first, but it only took me an hour to set it all up, and now it works perfectly and most importantly - all automatically.
Under "all trades" a typical formula is:
=IF($0227="m",$F227,)
O227 is the flag with the broker indicator: A=Ameritrade M=MBTraders
F227 is the computed current fair market value (less commissions and SEC fee) OR the sale amount (less commissions and SEC fee).
Under "open trades" and similarly under "closed trades" a typical formula is:
=IF($N227="o",$R227,)
N227 is the column with the "open" or "closed" flag
R227 is the cell [=IF($0227="m",$F227,)] (above)
---------------------------------------
Could it, or another program, do so quickly and without excessive manipulation and be highly user customizable?
Mine is highly user customizable, basically since I have columns with flags for everything I care about, I can get just about anything I want.
Mine is not set up to do subtotals BY TICKER. When I am curious there is some "excessive manipulation" required: I insert a few rows after the last line of say ONSL then I click the "summation" button, make sure the range is including just ONSL and I have my answer. I then exit the spreadsheet w/o saving.
---------------------------------------
That's what I am still hoping to find - it's just got to be out there. I use my own spreadsheet too, but it should be easier and prettier that that.
Hey, MY spreadsheet is "damn pretty"!! Thank you very much!
I have colors for open items, big bold "day's profit(loss)", "hour's profit(loss)" and "Y-T-D profit" at the top of the screen at all times. I print out the open items portion Monday through Thursday (this takes a little manipulation each evening). I print out the whole report each Friday.
Each morning I need to manually "set" the prior profit(loss) for each open stock so I'll get an accurate reading on the day's profit. (I have it "prove" the whole report profit vrs yesterday's profit AND then profit by security compared to yesterday).
This is my tool for keeping track of what's open, how I'm doing, etc. I keep it open all day long.
It also links to another spreadsheet I have where I have my Margin balance computed, Beginning excess Equity, Margin percentage, etc all available. Each morning I check these figures to the broker's figures to make sure everybody is in agreement.
One morning last month I saw my margin was not in agreement with MBT's accountinfo screen. Looking into it I saw someone else's purchase of 25 shares of AMZN was dropped into my account! I had better info than MBT seemed to have (they don't seem to know our margin loan balance per se) and we resolved the wayward trade.
Any suggestions for improvements appreciated!
Colin |
