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:

- 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”
- 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.
- On every second row, put the value in column I after the last value of that row. (should now be blank after step 1)
- 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)
- On every sixth row put the formula: =CG10+CG12-CG8-CG9 from the same range as the above step.
- On every seventh row put the formula: =CG13/CG10 from the same range as the above step.
- 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)
*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] reference: 8EP25921YV104724S

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

## Leave a comment: