Addition of RSU vest dates should not impede migration to new sheet versions

[hickeng]

When people contribute additional RSU vest date/price data, or if people have deleted/added rows themselves, it will impede ability to adopt a new sheet version.

Look into means for doing upgrade (why is it always upgrade problems - the repo's been in existence for less than a day!).

This may need ActionScript or Macros to avoid repeated re-entry of data.

[hickeng]

Added some guidance to README.md about how to add RSUs privately, and an undertaking not to break copy/paste compat between sheet versions again as it's such a pain.

Perhaps add a hidden date field to each row which is when it's added, and a version picker for the version of the sheet we're transferring from.

1. paste in the values - they'll be misaligned.
2. select the version of the source sheet
3. shifting any value in a row that's got a newer origin date than the source version down, displacing all lower cells down until stable.

That should result in correctly padding the old data into the new row layout... but maybe not worth it and doesn't help with privately added rows.

[ericgray]

If I understand this, the requirement is to adjust the table in the RSU sheet so that for each row, the "Market Value Per Share" is populated according to the date in the "Release Date" column.

One approach would be to create a new sheet that only contains two columns: Release Date and Market Value Per Share. (Copy them from the existing RSU sheet to columns A and B in the new sheet.) With a title in row one, this would be referred to as: RSUprices!$A2:$B and more values can be added as needed later, if they are contributed by others.

Then in the RSU sheet, use a VLOOKUP formula in column E, which is currently a static Market Value in rows 5 through N. More rows can be inserted, as long as formulas in all columns are copied.

For any (valid) date in RSU sheet column C, the price will be reflected in column E. Column C could also use data validation to only allow dates to be chosen from the table sheet.

This formula would be something like this in column E row 5, then copy down the sheet as needed:

=IF(C5<>"", IFERROR(VLOOKUP(C5, RSUprices!$A$1:$B, 2, FALSE), "No price found"), "")

With that tweak, you can have any number of rows on the RSU sheet, and the values can be copied and pasted from older versions of the spreadsheet. Only copy columns C, D, and F. The formula is in E.

[hickeng]

Thanks! I'll give that a go. I'm basically new to spreadsheets and wasn't aware of VLOOKUP.

It's a little more involved because of the following condition:

• there are potentially multiple vests on the same date, and the RSU sheet would need to have some kind of date instance count to allow for looking for the next match.
• the user could have rows that aren't in the sheet, in which case they need to be flagged - this just sounds like the reverse lookup so easily doable with an additional column with a prefilled formula that does that side of the check for the import data.

I'll definitely give it a go with VLOOKUP. If not my fallback will be AppScript - that's surprisingly easy once you start... although love/hate for javascript.

[ericgray]

Glad you think it might be useful. I believe it would handle multiple rows on the RSU sheet with the same date, and if there is a date with missing price info, the example formula above outputs "No price found" so that would be a hint to resolve the problem. I just sent you a Slack DM on the Turtle Pond with a prototype if you are interested.

Thank you for leading the charge on this!

[hickeng]

See #59

[hickeng]

Finally revisited the VLOOKUP and figured out why it was giving errors for some prices.

turns out that creating a view and sorting in ascending order does not meet the "must be sorted" requirement. You either need the raw data fully sorted or to wrap the range reference in SORT.

I now need to look into why there's a small numeric change when using the resolved data, but basically on track to have this in 0.1.7 🤞