Finding an error in google app script that runs a FIFO/LIFO calculation on a transactional history of an investment tracker spreadsheet

120 views
Skip to first unread message

Coin Atlas

unread,
Oct 31, 2021, 10:12:42 AM10/31/21
to Google Apps Script Community

Introduction

I have a spreadsheet and the idea of this spreadsheet is that any user can input his or her transaction history in the sheet "history" and based on this history, an overview is provided of a person's realized gains (=they have sold it in the past) and their unrealized gains (= never sold it). A script is run which then creates a report (sheet: Report) with the necessary data. A query table summarises this data in the summary of RG report sheet. Based on this summary, the position sheet is created, which shows all the current positions held by the user. The user can chose to run the script by going to the menu CoinAtlas>Build Report>LIFO or HIFO. Please see here the sample sheet.

What is FIFO and LIFO?

These are accepted accounting methods to calculate realized gains. FIFO (=first in, first out) & LIFO (=last in, first out). An example of FIFO: You buy 2 stocks of company A for 10 dollars each at date Y. At date Y+1 you buy 2 more stocks of company A for 15 dollars each. You hold 4 stocks of company A. You decide to sell 3 stocks of company A for 20 dollars each. Profit is then calculated as (3 * 20-(2 * 10+1 * 15)) = 60 - 35 = 25 dollars. The same scenario LIFO would be: 60 - 40 = 20 dollars.

Description of the problem

When building a report, for whatever reason, the stock MSTR and the cryptocurrency THETA is ignored and not shown in the output sheet "Report" when running the LIFO script and when running FIFO THETA is ignored (the only difference between the two scripts is the sorting in the beginning, one ascending and the other descending). The script was written by an experienced programmer who unfortunately no longer responds to this issue. With my beginner's skills in javascript/google app script. I cannot seem to find out why. I suspect it has to do with the input data, as with different data this error does not occur.

Here is a link to the script, included is the produced error and the output of tic.Sell.ForEach

Any advice on how to approach this problem will be much appreciated!

Clark Lind

unread,
Oct 31, 2021, 1:31:38 PM10/31/21
to Google Apps Script Community
The sample sheet you shared, has the code attached, and within that code is the api and your key on line 179. I removed the key from the script. 
If you don't have the key, let me know and I'll send it to you. (cwlind at gmail -dot- com).
Reply all
Reply to author
Forward
0 new messages