Looking through Anime stocks and sharing my Google Sheets note script

May 2024 ยท 9 minute read

Hello everyone,

Today starts of with a terrible pun. IBRK finally enabled access to the Praha Stock Exchange. There are only 28 companies listed, so it is quick and painless to check out. There were no hidden gems that I found, but given the small amount of stocks, it is definitely worth checking out.

In the spirit of Peter Lynch to know what you own - I am taking a journey through the world of anime and manga. For those that are unaware, manga are Japanese comics, and Anime are Japanese animation films or series. In contrast to most of the Western Animation tradition these are not just for Children, but can span any topic from erotica, action to horror and thrillers.

Anime has went from a niche product outside Japan, to a huge success around the world. Not only are anime series thriving, but anime films have become huge blockbusters, with Your Name earnings 357 million worldwide in 2016, ranking 22nd overall.

Here is a great interview with the creator of Your Name about his latest film Suzume.

Ghibli Studios who made masterpieces like Spirited Away used to be owned by a public company, but alas its private now.

I have my own Google Sheet, where I add companies to watch out for. I have tried Journalytic, but found that I need daily change data -  so see if there big movements, rudimentary data and most of all international tickers like Hong Kong and Japan. 

I have added a Menu with Stock Notes, which opens up a new menu -  where I can add the date, the ticker and the notes. It then saves the notes into a new worksheet. I then use a script to search for the ticker and display the notes for the ticker, sorted by date. You need to create a new sheet in the current workbook and then rename it to "Stock Notes".

To add it go to Extensions -> Add Scripts.

First one needs a new file and rename it to "sidebar.html".

These are the contents of that sidebar.html.This is what displays the sidebar in order to add the notes. It allows you to add the ticker, the date and your notes on the stock

<!DOCTYPE html> <html> <head> <base target="_top"> <script> function init() { document.getElementById("date-input").valueAsDate = new Date(); } </script> </head> <body onload="init()"> <h1>Add Stock Note</h1> <form> <label for="date-input">Date:</label> <input type="date" id="date-input" name="date-input"> <br><br> <label for="ticker-input">Ticker:</label> <input type="text" id="ticker-input" name="ticker-input" required> <br><br> <label for="note-input">Note:</label> <input type="text" id="note-input" name="note-input" required> <br><br> <input type="button" value="Add Note" onclick="addNote()"> </form> <script> function addNote() { var dateInput = document.getElementById("date-input").value; var tickerInput = document.getElementById("ticker-input").value; var noteInput = document.getElementById("note-input").value; google.script.run.addStockNote(dateInput, tickerInput, noteInput); document.getElementById("date-input").valueAsDate = new Date(); document.getElementById("ticker-input").value = ''; document.getElementById("note-input").value = ''; } </script> </body> </html>

Then you then need to create a second file in Apps Script called Code.gs. This does several things. The first one, is that the onOpen() and showSidebar() function allow the sidebar that we created with the HTML be actually displayed in the sheet.

Second it creates the addStockNote() function, which after clicking "Add Note", adds the notes to the spreadsheet and parses the date, so it can get sorted easily afterwards.

It also adds the getSortedNotesByTicker() function. We need that function in order to get the notes of a single ticker into our sheet. It also sorts it by time. Once you pasted it, press Run on the Code.gs file. It will ask you for the permissions of your Google Account. You have to accept that.

function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu('Stock Notes') .addItem('Add Note', 'showSidebar') .addToUi(); } function showSidebar() { var html = HtmlService.createHtmlOutputFromFile('sidebar') .setTitle('Stock Notes') .setWidth(300); SpreadsheetApp.getUi().showSidebar(html); html.append('<script>document.getElementById("date-input").valueAsDate = new Date();</script>'); } function addStockNote(date, ticker, note) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stock Notes'); if (!sheet) { sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('Stock Notes'); sheet.appendRow(['Date', 'Ticker', 'Note']); } var parsedDate = new Date(date); var formattedDate = Utilities.formatDate(parsedDate, SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), 'yyyy-MM-dd'); sheet.appendRow([formattedDate, ticker, note]); } function getSortedNotesByTicker(ticker) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stock Notes'); if (!sheet) { return 'No Stock Notes sheet found'; } var data = sheet.getDataRange().getValues(); var notes = []; var upperCaseTicker = ticker.toUpperCase(); for (var i = 1; i < data.length; i++) { if (data[i][1] && data[i][1].toUpperCase() == upperCaseTicker) { notes.push(data[i]); } } if (notes.length === 0) { return 'No Notes'; } notes.sort(function(a, b) { var dateA = new Date(a[0]); var dateB = new Date(b[0]); if (dateA > dateB) { return -1; } if (dateA < dateB) { return 1; } return 0; }); var output = ''; for (var j = 0; j < notes.length; j++) { var formattedDate = Utilities.formatDate(new Date(notes[j][0]), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), 'yyyy-MM-dd'); output += formattedDate + ' | ' + notes[j][2]; if (j < notes.length - 1) { output += '\n'; } } return output; }

In the sheet, where you want to have the overview. Add the =getSortedNotesByTicker(Ticker). It takes the ticker input and gets all the notes on that ticker sorted by date. I made it, so it would read the tickers from the A column.

That is it. I hope you found it useful.

ncG1vNJzZmiqn57AtHrSrpmsrJGYuG%2BvzqZmqWecpLystc2gZK2goqTCqLSMmqWipZViwLW7wqSqZquYlr%2BqusY%3D