Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 28

Thread: ($100) Compute monthly/annual/cumulative gains/losses for my option/stock trades.

  1. #1
    Join Date
    22nd November 2012
    Posts
    19

    ($100) Compute monthly/annual/cumulative gains/losses for my option/stock trades.

    Compute monthly/annual/cumulative gains/losses for my option/stock trades.
    1. The gain/loss for each year must match the schedule D from the broker.
    2. For open stock positions, it must show how many stocks for each symbol, acquired price, current price, and gain/loss.

    Options transactions:
    1. Indicated by transaction type “Sell to Open” and “Buy to Close” and with the description column containing the word “Put” or “Call”
    2. Add the net amount to the option total amount for the stock symbol for the transaction year.
    3. Add qty to the option total qty for the stock symbol for the transaction year.
    4. Add commission to the option total commission for the stock symbol for the transaction year.

    Stock transactions:
    1. Indicated by transaction field containing “Buy to Open” and “Sell to Close” and with the description column containing the word “Stock”.
    2. Add qty to the stock qty for the stock symbol for the transaction year.
    3. Compute cost basis for stock purchased by using “Fill Price”.
    4. Add commission to the stock commission for the stock symbol for the transaction year.

    Blank transactions:
    1. Indicated by transaction field containing blanks.
    2. Accumulate totals by year for each description type that has a blank transaction field.
    3. Contains the following description types:
    · Cash Journal
    · Money Market Dividends Paid
    · ACATS Journal Entry
    · Qualified Dividends
    · Short Account Mark To Market
    · ACH disbursements
    · Foreign Security Withholding
    · In Lieu Dividend /Substitute Payment
    · WIRE TRANSFER
    · Settled ACH Deposit
    · DOMESTIC WIRE FEE
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    1st March 2010
    Location
    God's Own Country
    Posts
    3,625

    Re: ($100) Compute monthly/annual/cumulative gains/losses for my option/stock trades.

    So you've mentioned what is what, but not how you want the output to look like, and also not how the computation should be exactly. I'll can look at this if you can provide more clarity on what exactly you need with proper specifications and logic.

    By the way, hope you've paid 10% to Ozgrid.

  3. #3
    Join Date
    22nd November 2012
    Posts
    19

    Re: ($100) Compute monthly/annual/cumulative gains/losses for my option/stock trades.

    Yes, I have paid 10% to Ozgrid.

    I have provided more information on output and computations. Some of these, I am also trying to understand, so there will be a little work in progress as we move forward and review results. Below is updated requirements with what I am looking for output. Updated excel file with a very basic output format is attached.

    Compute monthly/annual/cumulative gains/losses for my option/stock trades.
    1. The gain/loss for each year must match the schedule D from the broker.
    2. For open stock positions, it must show how many stocks for each symbol, acquired price, current price, and gain/loss.

    Options transactions:
    1. Indicated by transaction type “Sell to Open” and “Buy to Close” and with the description column containing the word “Put” or “Call”
    2. Add the net amount to the option amount for the stock symbol for the transaction year.
    3. Add qty to the option qty for the stock symbol for the transaction year.
    4. Add commission to the option commission for the stock symbol for the transaction year.
    5. Subtract option commission from option premium.
    6. Please see attached excel file for output format.
    a. There will be 1 spreadsheet for each year.
    b. It will contain both options and stocks.


    Stock transactions:
    1. Indicated by transaction field containing “Buy to Open” and “Sell to Close” and with the description column containing the word “Stock”.
    2. Add qty to the stock qty for the stock symbol for the transaction year.
    3. Compute cost basis for stock purchased by using “Fill Price”.
    a. For a specific stock buy transaction (Buy to Open), you will have to add all qty and fill price and compute fill price per share to determine what the cost basis is, i.e. stock buy price.
    b. For a specific stock sell transaction (Sell to Close), you will have to add all qty and fill price and compute fill price per share to determine what the stock sell price.
    c. Subtract stock sell price from stock buy price and multiply by stock qty to get “Stock P/L”.
    d. Subtract stock commission from “Stock P/L”
    4. Add commission to the stock commission for the stock symbol for the transaction year.
    5. For stocks still owned, the stock qty should show a non zero number. Calculate the “Stock P/L” by subtracting current stock price from stock buy price and multiply by stock qty. Subtract stock commission from “Stock P/L”

    Blank transactions:
    1. Indicated by transaction field containing blanks.
    2. Accumulate totals by year for each description type that has a blank transaction field.
    3. Contains the following description types:
    · Cash Journal
    · Money Market Dividends Paid
    · ACATS Journal Entry
    · Qualified Dividends
    · Short Account Mark To Market
    · ACH disbursements
    · Foreign Security Withholding
    · In Lieu Dividend /Substitute Payment
    · WIRE TRANSFER
    · Settled ACH Deposit
    · DOMESTIC WIRE FEE
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    1st March 2010
    Location
    God's Own Country
    Posts
    3,625

    Re: ($100) Compute monthly/annual/cumulative gains/losses for my option/stock trades.

    Can you explain 'Subtract option commission from option premium' in the Option Transation section

  5. #5
    Join Date
    1st March 2010
    Location
    God's Own Country
    Posts
    3,625

    Re: ($100) Compute monthly/annual/cumulative gains/losses for my option/stock trades.

    I think it will be better if you post the sample output with an example for any one stock (SWN maybe).

  6. #6
    Join Date
    22nd November 2012
    Posts
    19

    Re: ($100) Compute monthly/annual/cumulative gains/losses for my option/stock trades.

    Quote Originally Posted by S M C View Post
    I think it will be better if you post the sample output with an example for any one stock (SWN maybe).
    Yes, I agree! I will fill out the sample output with some data.

    Also, the commission is already subtracted from the net amount, so no need to subtract commission from net amount. Sorry, my mistake.

    Filling out the sample output should help.

    Cheers,
    Harry

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    22nd November 2012
    Posts
    19

    Re: ($100) Compute monthly/annual/cumulative gains/losses for my option/stock trades.

    gnore "OPTASS", "OPTEXE" transactions

    Compute stock buy price and stock sell price, by adding the net amount for a transaction and dividing by the number of shares bought and sold respectively.


    If stock sell qty is equal to stock buy quantity, then compute stock p/l by (stock sold qty * stock sell price) - (stock buy qty * stock buy price).


    If stock sell qty is less then stock buy quantity, then
    compute stock p/l by ((stock sold qty * stock sell price) - (stock sold qty * stock buy price)) + (((stock buy qty - stock sell qty) * current stock price) - ((stock buy qty - stock sell qty) * stock buy price))


    If stock sell qty is more then stock buy quantity, then
    compute stock p/l by ((stock buy qty * stock sell price) - (stock buy qty * stock buy price)) + (((stock sell qty - stock buy qty) * current stock price) - ((stock sell qty - stock buy qty) * stock sell price))


    The data I gave you is a small sample size. You requested SWN output. It would not have added up, because there were some relevant transaction at the end of 2010. I added those transaction date to make it match and so that it makes sense to. BTW, once you have written the VBA programs, I have data from 2009 to 2012 current date. Updated sample data file is attached.


    Please feel free to ask question as needed.


    Cheers,
    Harry
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    1st March 2010
    Location
    God's Own Country
    Posts
    3,625

    Re: ($100) Compute monthly/annual/cumulative gains/losses for my option/stock trades.

    Couldn't look at this during the weekend. I will start looking at this after work today and get back to you with any questions / inputs.

  9. #9
    Join Date
    22nd November 2012
    Posts
    19

    Re: ($100) Compute monthly/annual/cumulative gains/losses for my option/stock trades.

    How goes, any idea on when we can get this completed?

    Regards,
    Harry

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    1st March 2010
    Location
    God's Own Country
    Posts
    3,625

    Re: ($100) Compute monthly/annual/cumulative gains/losses for my option/stock trades.

    You should be able to get a draft in 3-4 hrs time

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Weekly, Monthly and Annual report
    By cstark in forum EXCEL HELP
    Replies: 1
    Last Post: November 12th, 2011, 01:25
  2. Annual Cumulative Interest Payment between 2 dates
    By jowhee13 in forum EXCEL HELP
    Replies: 1
    Last Post: January 18th, 2011, 18:50
  3. Monthly Increase Based On Annual Target
    By ttanner in forum EXCEL HELP
    Replies: 3
    Last Post: November 17th, 2007, 00:47
  4. Replies: 4
    Last Post: January 3rd, 2007, 13:36

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno