GoogleSheets: Stock average price

[Antrr3]

Hi everyone,

I'm really hoping you can help me with this. I've worked on this for some time, but it seems like I'm going in circles. What I need is rather simple, and here it is:

1) You purchase 10 shares of AAPL stock at $110 per share. You have a buying average of $110 per share (duh), with an open invested equity of $1,100 on your imaginary balance sheet.
2) You sell 5 of those 10 shares at $125 per share. You receive $625 in funds. $75 goes to P&L as income. -$550 goes back to balance sheet to offset the asset sale. You now hold 5 shares at $110 per share.
3) You purchase 5 more shares at $105 per share. You now have 10 shares at an average price of $107.50 on your balance sheet.
4) You sell all 10 shares at $135 per share. You receive $1,350 in funds. $1,075 goes to your balance sheet to offset your asset sale. The remaining $275 goes to P&L as income. Balance sheet shows $0 for that particular asset.
5) Finally, you purchase 5 more shares of the same AAPL stock at $95 per share. Your balance sheet shows (or at least should show) an average price of $95 per share, and total invested equity of $950 on the balance sheet.

Can anyone help me do this on google sheets? I'm thiiiiiiiiiiiiis close to killing myself over this.

Thanks in advance for saving my life.

[hrlngrv]

I'm too lazy to do the arithmetic to check whether you're using FIFO, LIFO or dollar cost averaging to determine the profit on 2nd and subsequent sales. At least in the US for income tax purposes you must elect one method and stick with it. So, which are you using?

ADDED: if you mean average cost basis, see this.

[Keebellah]

Hi Anthony,
Is this the post you're talking about in your PM?
I'm no good with Google sheets and what version is 2004? A MAC version?
Like I mentioned; look here: https://www.excelforum.com/excel-pro...ml#post5501732

[Antrr3]

Hi Keeballah,

Yes, that is the post. I rarely ever use excel, but my desktop version is Microsoft Excel 2007. For all purposes, I use GS.
Almost all formulas are the same in google sheets and Microsoft excel. I can follow the differences if there are any and match on GS.

I did look at the post above, but I am unable to make heads and tails of it. I just need a small push.

Attached is a Excel version of my GS. Let's see if we can get what's in the OP on the spreadsheet. As long as it's on excel, I can translate them to google sheets even if the formulas are different. Thanks in advance.

[Antrr3]

Replying to hrlngrv

Thanks for trying. This isn't what I'm looking for. The income portion is easy. It's the running average that I can't get right. The reporting format also doesn't matter.
What I basically need is a formula or a set of formulas that
1) can calculate the average price of the shares (this is easy)
2) keep the average price of the shares the same when a portion of it is sold.
3) update the average price when new shares of the same stock are bought again, but the it should only factor in the remaining shares after the previous sell (not the entire lot) and the new shares bought.
4) reset the average to 0 when there are no more shares of that same stock left
5) start a new average for that stock when purchased again, disregarding all previous transactions and average costs.

The P&L portion is quite easy when the average price is correct. I have actually done this, and it works, but it only works when I manually open and close the positions and manually enter the transactions.
But now, I have coded my googlesheets spreadsheet to download all transactions via API from all my trading platforms, and I can't go one buy one opening and closing transactions. They need to be done automatically. The above 5 steps will do that.

[Keebellah]

My experience with GS is very limited, I do understand that the look is identical, that’s the same as Corel’s version that already allowed over 1 million rows while excel wax just looking a 35600+ rows
The vba code will however not be fully supported .
Will see if I can do something for you but don’t hold your breath

[Antrr3]

My experience with GS is very limited, I do understand that the look is identical, that’s the same as Corel’s version that already allowed over 1 million rows while excel wax just looking a 35600+ rows
The vba code will however not be fully supported .
Will see if I can do something for you but don’t hold your breath

If you can get it done in excel, I can translate to google sheets. Thank you

[Keebellah]

I modified the file form the the other post just for the functionality
The closest I get is this and when you register a Sale it will calculate the sale price based on what is not yet sold (in Stock), I assume that since this is about Buying and Selling Stock you can add your own percentage to the FIFO calculated sale cost.

[hrlngrv]

. . . This isn't what I'm looking for. . . .

Until you provide an actual example rather than poorly worded, unclear explanations, you may continue to get disappointing responses.

. . . What I basically need is a formula or a set of formulas that
1) can calculate the average price of the shares (this is easy)

Indeed. This is the easy part. If you're trying for average cost basis, something you have so far FAILED to confirm clearly but seems to be the case, all that's needed is the total number of shares and the total purchase cost (which I labeld value in my Google Sheets example) for those shares. The average cost basis for current holdings is then the latter divided by the former.

If your initial transaction is a purchase, then if that were 10 shares at 110 per share, the total cost would be 1,100 (ignoring commissions and other transaction costs), and the values of note would be 10 and 1,100. Simple.

If the 2nd transaction were also a purchase, then if that were 5 shares at 115 per share, the cumulative total cost would be 1,100 already held plus 575 for the additional 5 shares, so 1,675, and the total number of shares would be 15. Still simple.

On the other hand, if the 2nd transaction had been a sale, say 5 shares at 120, the cumulative total cost would be 1,100 minus 5 shares at the current average cost basis of 1,100 / 10 = 110, reducing the total cost to 550 and the number of shares to 5.

If the 2nd transaction were a purchase and the 3rd transaction a sale, both using the figures from the previous 2 paragraphs, then the sale in the 3rd transaction would be from a position of 15 shares at total cost of 1,675. Selling 5 shares would decrease the total cost by 5 times 1,675 divided by 15. That is, the average cost basis of the 5 shares sold would be 111.67 (simple rounding), so the total cost after the sale would be 1,675 - 5 * 111.67 = 1,116.65.

2) keep the average price of the shares the same when a portion of it is sold.

My mistake earlier. Sales don't change average cost basis. Sorry. Note that this is in fact the case in the Google Sheets example I provided.

3) update the average price when new shares of the same stock are bought again, but the it should only factor in the remaining shares after the previous sell (not the entire lot) and the new shares bought.

Understood, which is why I used the approach in the example I provided. I didn't calculate average share price as a separate column. I only kept running balances in columns D and E, D for number of shares, E for total value, which is actually cost basis. Formulas in columns D and E in row 2 are simplistic because I assume a purchase (buy) transaction would come first. From row 3 on, the formulas do different things for buy and sell transactions. For buy transactions, total shares in D4 are total shares in D3 plus new transaction shares in B4, and total cost in E4 is total cost in E3 plus new transaction shares in B4 times share price in C4. The result is that E4/D4 would be the new, updated average cost per share. For sell transactions, total shares in D4 are total shares in D3 minus new transaction shares in B4, and total cost in E4 is total cost in E3 minus new transaction shares in B4 times the average cost per share given by E3/D3.

4) reset the average to 0 when there are no more shares of that same stock left
5) start a new average for that stock when purchased again, disregarding all previous transactions and average costs.

My approach handles that implicitly. If you sell off remaining shares, the columns D and E values would both be 0, so they'd contribute nothing to the next purchase transaction. As long as you can't sell more shares than you hold, the approach in the example I provided takes care of this correctly.

. . . But now, I have coded my googlesheets spreadsheet to download all transactions via API from all my trading platforms, and I can't go one buy one opening and closing transactions.

Sorry, but the calculations you need to perform REQUIRE you to do exactly this. This can be automated with formulas. However, I'm not going to try without a reasonable example to work with. You'd need to provide an extracted example of the download you're working with.

Point is, to do this correctly, it's necessary to start with a purchase transaction for a given stock, which should be the earliest dated transaction for that stock. Then iterate through the transactions for that stock in date of transaction order, increasing numbers of shares for purchase transactions, reducing numbers of shares for sale transactions, and updating total cost/value with each transaction: for purchase transactions, by simply adding number of shares times share price to the total cost/value just prior to that transaction; for sales transactions, subtracting the number of shares times the ratio of total cost to total number of shares just prior to that transaction from the total cost/value just prior to that transaction.

There is no magic single formula which can do this. You need to use a tabular approach. Which means the real work would be done by INDEX, MATCH or VLOOKUP calls into your actual download data to convert them into usable tabular format. Once in tabular format, the calculations are simple. Which gets back to YOU needing to provide an extracted example of your download data.

[Antrr3]

Hi hrlngrv,

Wait a minute. You're actually on the right track. I'm not sure how I looked at it earlier, but anyway... The logic on the spreadsheet you made works. The only shortcoming is the ticker (stock name) specific average.

Unfortunately, the forum will not allow me to post any messages here that include links since I am rather new here, so I have to download the GS into Excel, but please note that all the formulas following columns A-E will be replaced by values only.

The data in Columns A through E is autopopulated via API. From here on, they are transferred onto the master spreadsheet via =Importrange, where they are situated into their appropriate sheets... Let me know if I can further clarify. I have spent so much time on these spreadsheets, that it's like my own two hands, so I sort of expect everyone else to be as familiar with the concepts and formulas in them as I am. Sorry for any confusion.

Again, I appreciate all efforts. I realize that you have no gain in this.

[Antrr3]

Hi Keebellah,

Thank you again for your help. Let me see what we're looking at here.

[Keebellah]

Hi Antrr3, PLEASE DO NOT REPLY WITH QUOTE. The poster can read what he/she posted and all it does makes your post unnecessarily extensive.

[Antrr3]

Understood, Keebellah. Thanks.