Microsoft Excel Q&A

[moreld]

PARCEL 1. I buy 1000 shares in company AAA at $1. The brokerage is $20
PARCEL 2. I buy a further 2000 shares in AAA at $2. The brokerage is $30
PARCEL 3. I sell 2600 in AAA at $3. The brokerage is $40.

Before reading on please note I am not an accountant and have never played one on TV. This is not advice.

Some replies in this thread have taken a simple averaging approach, while that is acceptable for your own record keeping it is not acceptable to the ATO. I suggest keeping records one way is easier and thus you may as well do it the way the ATO wants.

Generally speaking you can only parcel share purchases of one company if they are bought on the same day. So in your case as in most cases you can't parcel your purchases together.
Your records for the ATO should look like this:
PARCEL 1. I buy 1000 shares in company AAA at $1. The brokerage is $20. Cost Basis is 1000*1+20 = 1020 / # shares for per share costs is 1020/1000 = $1.02

PARCEL 2. I buy a further 2000 shares in AAA at $2. The brokerage is $30
Cost Basis is 2000*2+30 = 4030 / # shares for per share costs is 4030/2000 = $2.015

PARCEL 3. I sell 2600 in AAA at $3. The brokerage is $40. The ATO is generous in that it lets you decide how to split the sale, i.e. you can choose any logic you like.
I'll assume you want to minimise your current CGT (if you had losses you may wish to maximise, or if you anticipate higher income in future etc).
So, sell 2000 at $3 and apportion 2000/2600ths of the commission ie 30/2600*2000 = 23.08. So receive $6000-23.08 = $5976.92. Minus the original cost basis of 4030 gives CGT of $1946.92 on that parcel.
Then next parcel 600 shares at $3 and remaining commission (30/2600*600) of 6.92 for total received of $1793.08. Minus cost basis of parcel 1 (600*1.02) 612 = $1181.08.

It is very easy to keep track of this on a spreadsheet, or most financial software do it for you.

 

[Gringotts Bank]

Hmm this is trickier than I thought. pixel you're right, the column I want to sort are all calculated values and they won't sort. I actually want the other columns to shift along with my original sorting, if that makes sense, because one of the columns is "stock code".

Jeff can u explain how to do that without mucking up the calculations, and also getting the stock code to move as well? I'm a total noob at excel sorry.

 

[stacks]

Hmm this is trickier than I thought. pixel you're right, the column I want to sort are all calculated values and they won't sort. I actually want the other columns to shift along with my original sorting, if that makes sense, because one of the columns is "stock code".

Jeff can u explain how to do that without mucking up the calculations, and also getting the stock code to move as well? I'm a total noob at excel sorry.

If you copy both columns of values then use paste special to paste all values

select columns > copy > paste special (from top menu) and tick 'values'

Then you should be able to sort by whatever column you like using sort function

 

[pixel]

Hmm this is trickier than I thought. pixel you're right, the column I want to sort are all calculated values and they won't sort. I actually want the other columns to shift along with my original sorting, if that makes sense, because one of the columns is "stock code".

Jeff can u explain how to do that without mucking up the calculations, and also getting the stock code to move as well? I'm a total noob at excel sorry.

That's even easier, GB

• You highlight the entire table.
• If your top row contains the column titles, e.g. 'code'. 'name', 'price'... then you can include that row as well.
• You click on menu option Data, Sort, and tick "table contains column titles" or whatever the tick box says.

Then you select the column that you want to sort by, and it's doing the correct sort, including the stock codes, names, etc...

And no: You don't have to create a new table or copy/paste special anything. The formulas will be fine (unless they're totally stuffed, but I can't imagine how one could "achieve" that)

 

[skc]

Hmm this is trickier than I thought. pixel you're right, the column I want to sort are all calculated values and they won't sort. I actually want the other columns to shift along with my original sorting, if that makes sense, because one of the columns is "stock code".

Jeff can u explain how to do that without mucking up the calculations, and also getting the stock code to move as well? I'm a total noob at excel sorry.

An even simpler method is to highlight the entire table (all column and rows) and apply filters (Data > Filter). Then in each drop down arrow you can choose to sort, show some and not others etc.

 

[Gringotts Bank]

Thanks very much everyone. Have tried each of those methods!

 

[pixel]

Have tried each of those methods!

... and ?? Did they work ??? That would be really interesting to know :

 

[Gringotts Bank]

Yes, they worked. I preferred your method.

 

[Gringotts Bank]

Me again.

I have a column of numbers, and I want to round them to the nearest 0.05, so that;

36.2 => 36.0
36.7 => 36.5
36.8 => 37.0

Anyone know a formula for this?

Thanks

 

[Logique]

The Round function might be the one GB.

Excel user tips:
https://encrypted.google.com/url?sa...svG3Dw&usg=AFQjCNEs_xSqleZpIJz6g2oI6ZXDNqZD8g
- Round values to the nearest fraction

 

[skc]

The Round function might be the one GB.

Excel user tips:
https://encrypted.google.com/url?sa...svG3Dw&usg=AFQjCNEs_xSqleZpIJz6g2oI6ZXDNqZD8g
- Round values to the nearest fraction

That rounds the number for display purpose only, but the number in the cell still has the decimals.

If you actually wanted integer you need the trunc (truncate) function.

 

[pixel]

Me again.

I have a column of numbers, and I want to round them to the nearest 0.05, so that;

36.2 => 36.0
36.7 => 36.5
36.8 => 37.0

Anyone know a formula for this?

Thanks

multiply by 20, round the result - if you do want to round, otherwise use trunc - and then divide by 20 again.
e.g. value in A1, function in B1 must read =Round(A1*20,0)/20

 

[Gringotts Bank]

Thanks Logique, I ended up using "=round(B1/0.05,1)*0.05" and that worked nicely. A variation of what pixel says, the same thing done differently by the looks.

As a follow up, if the value in my original column is <.1, I don't want to apply any rounding. Does excel have if/then/else type statements?

 

[pixel]

Thanks Logique, I ended up using "=round(B1/0.05,1)*0.05" and that worked nicely. A variation of what pixel says, the same thing done differently by the looks.

As a follow up, if the value in my original column is <.1, I don't want to apply any rounding. Does excel have if/then/else type statements?

yes, you can say =if(a1<=0.1,a1,round(a1*20,0)/20))
the syntax is if(condition,formula_if_true,formula_if_false)
and it can be nested inside a cell formula

 

[Gringotts Bank]

Thanks pixel.

 

[alexc2005]

Whats the best external website to import data from?

Currently using yahoo finance, but they only go down to 2 decimal places.

Which really isnt good enough as alot of my shares need 3.

Any other sites that are compatible with excel?

 

[pixel]

Whats the best external website to import data from?

Currently using yahoo finance, but they only go down to 2 decimal places.

Which really isnt good enough as alot of my shares need 3.

Any other sites that are compatible with excel?

Did you know that you can "open" any web page in Excel?
Simply plug any URL into an "Open" macro, and Excel will read it as a "spreadsheet".
Then you simply need to do a "find" that navigates you to the cell range that has the data you're after.
I've been using that method for years and get my data from a wide range of sources - ASX, BigCharts, LME, NSX, Yahoo, ... pick the most appropriate "horses for courses"

 

[alexc2005]

Did you know that you can "open" any web page in Excel?
Simply plug any URL into an "Open" macro, and Excel will read it as a "spreadsheet".
Then you simply need to do a "find" that navigates you to the cell range that has the data you're after.
I've been using that method for years and get my data from a wide range of sources - ASX, BigCharts, LME, NSX, Yahoo, ... pick the most appropriate "horses for courses"

Thanks for the reply

Yeah i have been doing that, thats how i got Yahoo figures. But with ASX i have to select the whole page, which is a bit overkill. I should be able to select just the price table but it wont let me. So just wasnt sure if there was another way or another website that did come up properly in excel as a "spreadsheet".

 

[skip9]

Hey Guys,
Looking to import data from the internet to create a price chart of selected companies. Whats the easiest way of doing this, i have "googled" it and there is steps for previous versions to import data via web query but i am using the new 2010 excel and can't find this option.

 

[davede]

Hi Skip,

WebQuery is still in 2007/2010 they just relabelled it a little bit.

On the ribbon:

Data > From Web (2nd From The Left)