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:
- Tools
- Script Editor
- Add file
- Name it SAMPLE
- 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:
- Loading from mobile is not reliable. You may have to erase the cell and undo, or reload, or get on a laptop/desktop.
- Sorting recalculates the numbers and sometimes it stops working. I just don't sort anymore, but it'll eventually fix itself.
- It doesn't calculate if you change values. Just erase the cell and undo, it'll do it correctly.
- 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.
