PrefInvestor1
27 Jan 2018
Re: Slight Correction - Akis Hi Again Akis,Yes it’s increasingly obvious that I should have gone for the “list of transactions” approach myself. However I made a change to my investment spreadsheet to make my break-even price include the profit/loss from any partial sells, wasn’t difficult - just needed about 3 new columns in my “buys” table. Tested it and it seems to work ok.I have found a way of listing any content that I like here. I became frustrated a while back at not being able to post pdfs or charts to accompany my posts. I now use OneDrive which gives you a fairly big chunk of web space when you open an account and it’s free and you can use it from any type of computer. All you do is copy what you want to your onedrive webspace, login to onedrive through your browser and use the “share” facility provided there to generate a URL link that you can then paste into your post (removing the s from https makes the links immediate clickable).Typically I have only posted pdfs and images because if I post excel files I fear that might be problematic if people don’t have the latest version (I have office 365). But other than that there is no reason why you can’t post .xls files or anything else for that matter.ATBPref
Akis1999
26 Jan 2018
Re: Slight Correction - Akis Hi Pref,Since you (we) are using Excel we can be accurate to a fraction of a penny. Therefore we ought to calculate (factor in) charges. fees. interests, etc.I have a tradelist. It is simply a table listing all transactions, in chronological order as they have occurred. Buys, sells, interests, charges, dividends. A new transation, eg a SELL, is simply another line in the long list.There is VBA code which parses that trade list and generates the current position. It also generates the old positions, eg closed. I cannot attach anything here, I would send you photos if I could.There is a front page where you can see the current positios along with receivables (eg declared dividends or bond interests), CASH, and past performance. There is a daily snapshot of the portfolio position, ie today's mark to market value, ITD etcThis is a spreadsheet for each trading account.There is a consolidated spreadsheet which tracks investments across all accounts. To get the bigger picture.Finally I also have market data, historical data, watchlists with charts and dividends data. The thing is it does not really matter how as long as you can make a profit. Even if you buy a share blind and sell at a random time, if you make cash that is all that matters.
marktime1231
26 Jan 2018
MS v. DB Still waiting for the volte-face from DB who last shouted down SSE to 1250 two weeks ago and gone quiet ... pulling that rotten tooth would be such a relief ... it is probably a team effort but surely a senior executive decision to be so aggressively bearish.Meanwhile Morgan Stanley have slammed down the gauntlet re-iterating their Overweight rating and a target price of 1670. Even I think that is fanciful, no doubt with marketing in mind, but a welcome boost.Have we bottomed?Wish we could put rationale to these announcements, presumably you have to pay for that, but such a gulf between these opinions means at least one them is hopelessly wrong.
PrefInvestor1
26 Jan 2018
Re: Slight Correction - Akis Hi Again Akis,I have been mulling the issue of how I could incorporate a partial sell into the break-even price that I maintain on my investment spreadsheet. My break-even number already incorporates all dividends received on that position but doesnt incorprate any component, either profit or loss, from a partial sale. As I said I very rarely do those, but it has happened - albeit rarely.My problem comes from what is clearly an inadequacy in my data model. I did make major changes earlier in the year when I re-built it all from the ground up to use Excel tables and to remove all of my VBA code (to make undo work again !!). But the issue comes from the fact that I have a worksheet which is really just a list of all my buys, I then have a field which is a sell date - which if completed means that buy is closed. Of course I can have multiple buys in the list for the same stock so the overall position isnt closed unless all those are sold. I probably ought to have changed all this to be a list of all of the buy and sell transactions - but I didnt and it would be a big change now.On those few occasions when I have done a partial sell I have gone back and split my buy row on that worksheet into two, allowing me to close one of them off. Inelegant maybe but it works.Haven given it some thought I think a couple of new columns in my buys sheet should enable me to add in this calculation into my break-even field. Still keeping the splitting method outlined earlier.So thanks very much for your input, I will try and incorporate this change ASAP. Not that I plan on doing them any more often you understand, but should I do so at least the break-even point will be more accurate.Thanks AgainPref
PrefInvestor1
25 Jan 2018
Re: Slight Correction - Akis Hi Akis,Some interesting concepts that you have described there. I do some of those things but not others and I have a very simplistic mode of operation when it comes to equities which means that I dont ask myself some of the questions that you have identified.For example:-1. Every penny I have is ISAd up. I never have to do tax returns or worry about CGT, dividend tax or whatever. So my investment spreadsheet doesnt worry about any of these things as they are problems which never come up.2. With equities I typically buy and hold. If they drop by 10% I sell and invest elsewhere. I rarely if ever average down. I rarely if ever do partial sells either, has been known but not often. But then I never have more than about £6,000 (and often only £3,500) in any one equity anyway. Over 80% of my money is in preference shares that I never sell but just hold forever to collect the income.3. I do maintain a break-even price on each stock on my spreadsheet - this figure includes all dividends received on the stock, and as you say tells me the price at which I can afford to exit without making a loss.4. I also keep a position open in a stock till it is fully closed. But as I dont usually do partial sells thats a pretty binary thing, I either hold an equity or I dont. Occasionally I buy more than one tranche of something though. The position tells me my gain/loss on the stock covering multiple buys (if any) and total dividends. It doesnt track selling part of a holding as I never do that.I have no accounting background. My methods and calculations are my own and do what I need them to do. But they seem to work OK. No doubt they could be better !!!. Doubtless my handling of equities could be more sophisticated, but to date I have not seen a need.ATBPref
Akis1999
25 Jan 2018
Re: Slight Correction - SAG/Akis If we define Average Price as the price at which if I sell all my holding I will break even.Then when you sell a part and you have made a profit, this profit brings your Average Price down on the remaining because now you need to sell for less than originally bought in order to break even.Of course this assumes the notion of the "Position" which means an exposure to a stock, which could be profitable or loss making. This Position usually exists until you dispose of all the stock.You can buy shares and sell shares, all you do is you adjust the number of outstanding shares and the average price of this position.This is the calculation needed to tell you if you are profitable or loss making.However for the tax man you may employ a different accounting scheme - the tax man wants to see a "FIFO" transaction, so when you sell some shares the taxman assumes you sold your Oldest holding.If the Position is very fluid, for example one you keep transacting on, you may want to employ a different accounting method, maybe with arbitrary monthly timelines etc.In other cases you may want to "Close" and "Reopen" a Position in order to solidify and re-direct a profit, or a loss. Even if it is done on the spreadsheet and not on the market. For example every day your Position opens in the morning and closes in the evening, meaning that every day you make or lose some money, for as long as you maintain the holding. Spreadbet works this way.However the most intuitive and logical division of your holdings is by stock and the Position is "Open" for as long as you hold 1 share on this stock. The Position "Closes" when you have 0 shares left. At that moment you can calculate the P/L. It is all different methods of accounting.However, again, if one asks you "how did you do with SSE", the correct answer would be "I have made 10K" implying I have sold everything and the P/L was 10K. If I have sold just a part and have made 10K on the transaction but I am still holding, then I can not give a good answer because I am still exposed. I could then "Mark to Market" ie assume that I Sell ALL my shares at the prevailing price and thus Close my Position, then calculate the final P/L and report back "I am making 10K".On my own spreadsheet I keep a position open until it is fully closed. My average price is the amalgamation of all costs to buy and hold the shares and of any incomes received since (eg partial sells and dividends or coupons). I calculate an average price taking everything into account and it is the price I can sell my remaining holding at so as to break even. I believe this is a most important price to know instantly because it shows you the price you need to achieve in order to Exit.Exiting is the most important aspect of trading, IMO.So one would keep one set of accounts for the tax man but a different set of accounts for the directors. And maybe a third set of accounts for the shareholders.
PrefInvestor1
25 Jan 2018
Re: Slight Correction - SAG/Akis Hi Again SAG/Akis,Just a last comment from me on all this if I may. From my perspective:-Selling doesn’t do anything to your average. You just end up with N less shares at the same average as before but with some cash. The sale might constitute a profit or a loss depending on whether you sold above your average or not. The gain/loss is given by:-Gain/Loss = no of shares sold * (selling price – average)Buying can increase or decrease your average depending upon whether you buy at above or below your current average. The formula for your new average after a buy is given by:-Avg(new) = (N(existing)*Avg(existing) + N(bought)*Price)/(N(existing)+N(bought))From this formula it’s pretty easy to see that buying at the existing average price leaves the average unchanged.Going back to your transactions. At step 3/5 you made ~£90 profit (£1001) which bought you 90 more shares but didn’t change your average as you bought at your existing average price of £1. At step 6/8 you again made about £90 profit (£1001) which bought you 202 more shares and reduced your average to £0.95 because you bought them at the lower price of £0.90. In this case you were averaging down.That’s how I see it anyway. Hope you both agree !.ATBPref
Akis1999
24 Jan 2018
Re: Slight Correction - SAG Hi SAG, as Pref said I also use Excel to track my investments, and it has grown to thousands of lines of VBA code as I feed real time marketdata and follow dividends etc.selling shares at £1.10 you have made a £0.10 profit on each share sold. The average price on the remaining shares is £1.00 less the profit made divided by the remaining shares.
Special A Gent
24 Jan 2018
Re: Slight Correction - SAG Hi PrefApologies regarding the abuse comments.It was not my intention to direct them at you, Sorry.Had a quick look at your PDF file and I can see what you have done.seperating the sell credit and recording the funds seperately appears to correct the averaging process.More then happy to rearrange my data recording proccess.Thanks for your help, your a gentleman.S.A.G.
PrefInvestor1
24 Jan 2018
Re: Slight Correction - SAG Hi Again SAG,I tried to follow your instructions but TBH its difficult to make sense of them. Then it occurred to me that I already produced a spreadsheet for this problem earlier today.You will find a link to a PDF printout of my spreadsheet here:-[link] course it contains my formulae and not yours and the answers are exactly as I described in my earlier post to you.ATBPref
PrefInvestor1
24 Jan 2018
Re: Slight Correction - SAG Hi SAG,Sounds dangerously like you are saying “I did this calculation in a spreadsheet so it must be right”.Sadly It is very easy to get spreadsheets wrong and a key principle that you should employ when creating one is to ask yourself the question “Does that result look sensible ?”. And if it doesn’t your spreadsheet is almost certainly wrong. In those circumstances you need to do a calculation on paper and make sure it’s right and then go back to the spreadsheet and make whatever changes are necessary to achieve that result. Only then can you start to trust what the spreadsheet is telling you.Just believing what a spreadsheet tells you without going through this process is a very easy way of deluding/confusing yourself.I have a degree in computer science and worked as an IT consultant for 40+ years. So I have created the odd spreadsheet in my time and I know how easy it is to get them wrong !.When I get time I will try and work through the procedure that you have described to see if I can spot any obvious errors (unless akis gets there first). But I don’t know when I will have the time to do this as I have quite a lot of things to do over the next 3-4 days. The earliest I might be able to give it my attention is probably Sunday.ATBPref
Special A Gent
24 Jan 2018
Email what a good provider offers Dear Mr SA Gent, soon you’ll have the chance to buy or sell UK shares for just £3.95 commission per online trade for a limited period. That’s a 68% price reduction on the usual price of £12.50 – and it’s only available on 30th January. The main offer period is from 12:15pm to 2:15pm. But as you’re one of our most loyal customers, we’d like to offer you an extended period from 8am to 2:15pm. For deals before 12:15pm, we’ll refund the discount of £8.55 per trade to your account within five working days. We're also applying the same great offer to trades on International stocks made between 8am-9pm. So, if you trade during this time, the normal commission charge of £12.50 will be applied to those trades, but we will then refund the discount of £8.55 per trade to your account within five working days. So make sure you're ready. We wanted to let you know that from next month our monthly emails about this offer will stop. But don’t worry, the offers will still continue and you can find out all the information you need to know ahead of the offer. Save this link into your favourites to keep up to date.
Special A Gent
24 Jan 2018
Re: Slight Correction - SAG Hi Akis Thanks for the reply and yes it is strange yes you would expect average to be less, so her my answer.Let me offer you this as an explanation and suggest you use an excel spread sheet and do the sums for your selfAfter all I only achieve City in Guilds Level 4 in Mathermatics so let others more qualified do the bizHere goes.A shread sheet is coded and uses software to perform the tasks what you know as a profit, the sofware only sees numbers.So could I ask you or someone to use excel and put the information into the following columns as I have shown in the example.Column A Header to read Quantity = number of shares.Column B Header to read Price per share include all costs. This all costs should be measured by the total costs divided by the number of shares bought or soldFor this example please assume the costs have been added.Column C Header to read Total Price. For a buy this figure will be Column A times Column B and the resultant number/currency will be the same figure that you paid to your broker. So in my example 1000 shares times (X) the SP and costs are £1 then the total price is £1000.00Column D Header to Read Total shares. This will be the sum total from Column A2To calculate repetative Column fields A3+A4+A5+A6 and so on use the AddSum button function key allows you to put the coding in to add respective Column D fields so here is what the coding will look like.Col and Field D2 will show A2Col and field D3 will show A2+A3 or alternatively A2:A3 it means the same.Col and field D4 will show A2+A3+A4 or alternativly A2:A4 it means it's summed up A3 also but does not show it in the coding.When you put in the number of shares bought show as a posertive e.g. 1000When you sell a number of shares show as a negative e.g. -910 use subtract in front of the number.Column E to read Total Paid Similar to column D but this time you are adding all the totals currency paid on previous purchases or sells. Same proceedure use the AddSum function key.No need to use the subtract (-) against a sell price , EXCEL will do that for you.Column F to read Average Price or even Combined PriceThis is the result from or =SUM D2/E2 or D2 divided by E2 expressed as a number.Use the percentage function key and it will show it as a percentage %To do this you need to place the curser box around the column and field F2Now using the curser arrow click the ADDSUM and type in D2/E2 and hit the return key/button, repeat the process for F3 D3/E3 and so on Akis same for you will you respond back and let me have your results/ feed back on the results.Regards S.A.G.
Akis1999
24 Jan 2018
Re: Slight Correction - SAG you said"Buy 1000 @ £1.0 = average price £1 per shareBuy 1000 @ £1.0 = average price still £1 per shareSell 910 @ £1.10 leaves 1090 shares = average price of £1.0909"Now this is instinctively wrong. How can you sell shares for more than you bought them (ie for a profit) and still increase your average price as if you've made a loss?Unless you are including stamp duty and broker's fees on the buys in which case, even before the sell, your average price is not £1 but higher. Your example still wrong anyway.
PrefInvestor1
24 Jan 2018
Re: Slight Correction - SAG Hi Again SAG,No problem. I am in the (unfortunate) habit of doing stuff in the middle of the night these days. Too easily done with the ipad sitting in bed. Never mind having a lie in is not a problem !.One thing I meant to ask you was that I never got to grips with the way that you were calculating your averages. So for example take your first three transactions:-Buy 1000 @ £1.0 = average price £1 per shareBuy 1000 @ £1.0 = average price still £1 per shareSell 910 @ £1.10 leaves 1090 shares = average price of £1.0909How did you get your figure of £1.0909 here ?. 1090 * £1.0909 = £1189.08 ?. What does this mean ?. Nothing to me. Would like to understand, if you have the time.ThanksPref
