Skip Navigation
Investing @lemmy.world _MoveSwiftly @lemmy.world

Google Sheet with live option tracking

cross-posted from: https://lemmy.world/post/704147

cross-posted from: https://lemmy.world/post/704145

Yes, live.

​

I've looked for this for a while and didn't find much. Being a stubborn ass (The Boondocks voice) I kept looking until I got it down. There are trackers for the options you've sold, call and put screeners, calculators to avoid CSP risk, holdings, and a singular location for cost basis so you don't do something dumb like Idk sell SPCE CCs at $20 when your cost basis is $22 but you thought it was $18. I definitely didn't do that.

​

Google Sheet

You'll need to add a custom script:

  1. Tools
  2. Script Editor
  3. Add file
  4. Name it SAMPLE
  5. Paste this into it:

The code is thanks to tanaike

Code source: https://stackoverflow.com/questions/64437503/importxml-not-producing-correct-values

function SAMPLE(url) {
  const res = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
  const tables = [...res.getContentText().matchAll(/(<table[\w\s\S]+?<\/table>)/g)];
  if (tables.length < 2) return "No tables. Please confirm URL again.";
  const values = tables.reduce((ar, [,table]) => {
    if (table) {
      const root = XmlService.parse(table).getRootElement();
      const temp = root.getChild("tbody", root.getNamespace()).getChildren().map(e => e.getChildren().map(f => isNaN(f.getValue()) ? f.getValue() : Number(f.getValue())));
      ar = ar.concat(temp);
    }
    return ar;
  }, []);
  return values[0].map((_, i) => values.map(r => r[i]));
}

The result: It returns a table, so you need to use INDEX with it in order to point to a specific row/column. The method itself is SAMPLE, which takes a URL and returns a table. So you'll need to use Concatenate in order to make up the URL for a Yahoo link.

Google Sheet function:

=INDEX(SAMPLE(CONCATENATE("https://finance.yahoo.com/quote/", $A7, RIGHT(YEAR($E7), 2), TEXT(MONTH($E7), "00"), TEXT(DAY($E7),"00"), IF(B7 = "PUT", "P", "C"), SUBSTITUTE(TEXT($M7,"00000.000"), ".", ""), "?p=", $A7, RIGHT(YEAR($E7), 2), TEXT(MONTH($E7), "00"), TEXT(DAY($E7),"00"), "C", SUBSTITUTE(TEXT($M7,"00000.000"), ".", ""))), 2, 3)

​

Known issues:

  1. Loading from mobile is not reliable. You may have to erase the cell and undo, or reload, or get on a laptop/desktop.
  2. Sorting recalculates the numbers and sometimes it stops working. I just don't sort anymore, but it'll eventually fix itself.
  3. It doesn't calculate if you change values. Just erase the cell and undo, it'll do it correctly.
  4. Not all options are available on Yahoo, especially if you're looking 30+ days.

​

Full page, two words: Fuck you good luck!

​

P.S. Yahoo dev: If you're seeing this, please let me be. This was already hard enough to put together. Plz.

0
0 comments