oh, the scandal!
Posted by scmfinance on January 18, 2007
it all started last night when i decided to add two commissions’ columns to my spreadsheet — one for when i buy and the other for when i sell. i use scottrade and so far for this experiment have been solely trading stocks under a $1, so the commissions are $7 + 1/2% of the principal. the function seemed simple enough:
=PRODUCT (# of shares column, entry/exit price column) *.005+7
the function worked just as it should have when it came to calculating my commissions on stocks i bought. but when i ran the function to calculate commissions on stocks i sold, excel would return an amount different from the amount scottrade had charged me — it simply didn’t make sense. so, i decided i would call scottrade and ask them why the numbers were not matching.
the representative was kind and informed me that the reason was because i was leaving out the tax the SEC places on sales. as if capital gains isn’t enough, the government has to tack on .0000307 to your commission. (that regulatory fee fluctuates although i’m not sure how often.) so, with this new-found information, i re-formulated my function:
=PRODUCT(# of shares column, entry/exit price column) *(.005+.0000307)+7
i would like to say that everything worked out, but of course, the scandal doesn’t stop there. i noticed that some of the commissions on stock sales were correctly calculated now, however, some were still a penny less in my spreadsheet as opposed to the amount scottrade charged. i quickly decided that it must be some sort of rounding error. from there, i concluded that while excel adheres to the universally practiced rounding laws (round down if the number is <5, round up if the number is >=5), scottrade does not and simply rounds all numbers up. so, i added the ROUNDUP function to my formula and what do you know? all of the commissions in my spreadsheet were now equivalent to those that scottrade charged.
now, i know what you’re thinking…”those are ridiculously small numbers,” but if you’ve ever seen the movie “office space,” you would understand 
anyways, just some food for scandalous thoughts…
Ray (proclivity) said
Hey SCM,
You got your excel sheet working eh? I have scottrade also and couldn’t get mine to take. I guess i am having the same problem you had. I guess i just don’t have it set up with all of the nuances. Friggin’ scottrade.
p.s. don’t worry about the loss……it’s all part of the game.
I’ll probably be playing by next week.
ray
scmfinance said
yeah, my sheet is working although i do anticipate possibly adding some more columns in the future. one column i have been thinking about adding would be a “settlement date” so i know when the funds are settled and obviously save myself a couple seconds in figuring it out. the problem is i would need to figure out a way to exclude weekends and holidays which could be somewhat more labor intensive than it’s worth.
if you need help formulating your sheet, let me know and i wouldn’t mind e-mailing my sheet so you can actually look at the functions.
Jak said
free database software