Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • ($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

  • #2
    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.
    Cheers,

    S M C

    Click To Read: How To Use Tags In Your Threads/Posts
    Please take time to read Forum Rules before posting
    Message To Cross Posters

    Comment


    • #3
      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

      Comment


      • #4
        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
        Cheers,

        S M C

        Click To Read: How To Use Tags In Your Threads/Posts
        Please take time to read Forum Rules before posting
        Message To Cross Posters

        Comment


        • #5
          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).
          Cheers,

          S M C

          Click To Read: How To Use Tags In Your Threads/Posts
          Please take time to read Forum Rules before posting
          Message To Cross Posters

          Comment


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

            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

            Comment


            • #7
              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

              Comment


              • #8
                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.
                Cheers,

                S M C

                Click To Read: How To Use Tags In Your Threads/Posts
                Please take time to read Forum Rules before posting
                Message To Cross Posters

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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
                    Cheers,

                    S M C

                    Click To Read: How To Use Tags In Your Threads/Posts
                    Please take time to read Forum Rules before posting
                    Message To Cross Posters

                    Comment


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

                      Where do you get the current stock price from
                      Cheers,

                      S M C

                      Click To Read: How To Use Tags In Your Threads/Posts
                      Please take time to read Forum Rules before posting
                      Message To Cross Posters

                      Comment


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

                        Will wait till tomorrow for a confirmation on the current stock price
                        Cheers,

                        S M C

                        Click To Read: How To Use Tags In Your Threads/Posts
                        Please take time to read Forum Rules before posting
                        Message To Cross Posters

                        Comment


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

                          And how did you figure out SWN stock commission to be $5?!
                          Cheers,

                          S M C

                          Click To Read: How To Use Tags In Your Threads/Posts
                          Please take time to read Forum Rules before posting
                          Message To Cross Posters

                          Comment


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

                            Harry, any updates on this? I will get busy during the weekend.
                            Cheers,

                            S M C

                            Click To Read: How To Use Tags In Your Threads/Posts
                            Please take time to read Forum Rules before posting
                            Message To Cross Posters

                            Comment


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

                              What is happening? You said I would get a draft in 3 - 4 hrs, and that was 5 days ago. If you cannot do it, just let me know, I can find somebody else to do it for me. This is taking way too long!

                              Harry

                              Comment

                              Working...
                              X