Announcement

Collapse
No announcement yet.

$40 USD for a tool automating the transforming of data set

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

  • $40 USD for a tool automating the transforming of data set



    Hi,

    I need a tool to transform raw data I collected of option prices. I attached the file I want to use as a template for in total 100 companies. Also I attached a file to get a sense of what it should look like, but


    Steps to transform option data set into useful data set:
    1. Delete every cell that has 1 cell with value “NA” below itself and 2 cells with value “NA” above itself (this cell gives the price of asset while it is either before the issue date or after the strike date), and also those cells above and below with value “NA”
    2. Replace every second cell in column H with “PV(X)”, every sixth cell with “vote value”, every seventh cell with “vote yield”, starting from row 8.
    3. On every second row, put the value in column I after the last value of that row. (should now be blank after step 1)
    4. If the location of the value put in at step 3 is e.g. CH9, put the following formula: =$CH9/(1+(CG$5/100))^(($CH$4-CG$4)/365) as the formula of the cell left to the value created in step 3, and repeat all the way to the first value of that row (like double clicking on the right lower corner would normally repeat downwards)
    5. On every sixth row put the formula: =CG10+CG12-CG8-CG9 from the same range as the above step.
    6. On every seventh row put the formula: =CG13/CG10 from the same range as the above step.
    7. On the last row, for every column (day), put the daily average of every eighth cell in that column, e.g. =AVG(V15;V23;V31;V39)
    8. For every month compute the monthly average using daily averages.

    So basically: I need the vote yield, defined as (vote value/value underlying asset (every third row)). Where vote value is defined as

    PV (Vote (T)) = S + p - c - P V ( X )
    = underlying stock price + put option price – call option price – Present value of an asset with the same strike price (found in column I) and same maturity date (cell after last value of every fourth row (Column H also gives that date))

    I created 4500 rows, so the template can be used with data sets of other companies, which may have more option data. So it needs to be made so I can use it again and only replace Cell H7 and below and right of H7.

    Maybe it could be possible to create command buttons for every step, so oversight can be remained.

    I hope someone can help!

    I paid the 10% to [email protected]d.com reference: 8EP25921YV104724S

    The template file is too big to upload, here is a link: https://ufile.io/2ckm2


    Attached Files

  • #2
    Hi,
    I can take a look at this and let you know if I have any questions.
    By when do you need it ?

    Comment


    • #3
      Great, maybe you could do it by friday, i will be able to have a look at it on saturday, would be nice to have the final work on monday, but tuesday would also do!

      Comment


      • #4
        Hi,

        Coud you provide a clear example for step 1 by using an extract or your template file. Let says the first 50 rows and a few columns
        If that extract, can you highlight in color the cells you need to remove.

        Also, where do you get the values of rows 1-3-4-5-8

        Thanks.

        Comment


        • #5
          Hi, i’ll get back to you on saturday, is that okay?

          thank you

          Comment


          • #6


            Yes that's fine.
            I sent you a private message also. I'd like you to send you the file so can validate step 1.
            Thanks,

            Comment

            Working...
            X