Traditional web-based debate tabulation systems like Tabbycat, offer robust features but often pose high costs and accessibility barriers that limit participation and the smooth organization of events. In this work, we present Tab XYZ, a novel debate and public-speaking tabulation platform built on Google Sheets, as a cost-effective alternative to conventional systems. We deployed Tab XYZ’s cloud-based features like Google Apps Script automation, Google Forms for data input, real-time collaboration, to replicate core functionalities of standard tabulation software without the need for dedicated servers or paid licenses. The proposed system was evaluated in five tournaments constituting a total of 435 participants, and compared against a popular web-based platform on key metrics including setup time, user satisfaction, reliability, and error handling. Results indicate that Tab XYZ eliminated all licensing and hosting costs while achieving user satisfaction scores (overall average 4.7 out of 5) comparable to the conventional system (4.6 out of 5). Tab XYZ also demonstrated robust data security and offline-capable error recovery by leveraging Google’s infrastructure. These findings illustrate a viable pathway to leverage readily available IT tools like spreadsheets and cloud services, to create innovative solutions for specialized domains, avoiding the cost and complexity barriers of traditional approaches.

My Grandpa asked if the programs I was using could calculate his Golf League’s handicaps, so I decided to play around with SQL and Google Sheets to see if I could functionally recreate what they were doing.

The goal is to calculate a player’s handicap, which is the average of the last six months of their scores minus 29. The average is calculated based on how many games they have actually played in the last six months, and the number of scores averaged correlates to total games. For example, Clem played over 20 games so his handicap will be calculated with the maximum possible scores accounted for, that being 8. Schomo only played six games, so the lowest 4 will be used for their average. Handicap is always calculated with the lowest available scores.

This league uses Excel, so upon receiving the data I converted it into a CSV and uploaded it into bigQuery.

First thing I did was change column names to best represent what they were and simplify things in the code. It is much easier to remember ‘someone_scores’ than ‘int64_field_number’. It also seemed to confuse SQL less, as int64 can mean something independently. (ALTER TABLE grandpa-golf.grandpas_golf_35.should only need the one RENAME COLUMN int64_field_4 TO schomo_scores;)

To Find the average of Clem’s scores: SELECT AVG(clem_scores) FROM grandpa-golf.grandpas_golf_35.should only need the one LIMIT 8; RESULT: 43.1

Remembering that handicap is the average minus 29, the final computation looks like: SELECT AVG(clem_scores) - 29 FROM grandpa-golf.grandpas_golf_35.should only need the one LIMIT 8; RESULT: 14.1

Find the average of Schomo’s scores: SELECT AVG(schomo_scores) - 29 FROM grandpa-golf.grandpas_golf_35.should only need the one LIMIT 6; RESULT: 10.5

This data was already automated to calculate a handicap in the league’s excel spreadsheet, so I asked for more data to see if i could recreate those functions.

Grandpa provided the past three years of league data. The names were all replaced with generic “Golfer 001, Golfer 002, etc”. I had planned on converting this Excel sheet into a CSV and manipulating it in SQL like with the smaller sample, but this did not work.

Immediately, there were problems. I had initially tried to just convert the file into a CSV and drop it into SQL, but there were functions that did not transfer properly from what was functionally the PDF I had been emailed. So instead of working with SQL, I decided to pull this into google sheets and recreate the functions for this spreadsheet. We only need the most recent 6 months of scores to calculate our handicap, so once I made a working copy I deleted the data from before this time period. Once that was cleaned up, I started working on a function that would pull the working average from these values, which is still determined by how many total values there were. This correlates as follows: for 20 or more scores average the lowest 8, for 15 to 19 scores average the lowest 6, for 6 to 14 scores average the lowest 4 and for 6 or fewer scores average the lowest 2. We also need to ensure that an average value of 0 returns a value of 0 so our handicap calculator works. My formula ended up being:

=IF(COUNT(E2:AT2)>=20, AVERAGE(SMALL(E2:AT2, ROW(INDIRECT("1:"&8)))), IF(COUNT(E2:AT2)>=15, AVERAGE(SMALL(E2:AT2, ROW(INDIRECT("1:"&6)))), IF(COUNT(E2:AT2)>=6, AVERAGE(SMALL(E2:AT2, ROW(INDIRECT("1:"&4)))), IF(COUNT(E2:AT2)>=1, AVERAGE(SMALL(E2:AT2, ROW(INDIRECT("1:"&2)))), IF(COUNT(E2:AT2)=0, 0, "")))))

The handicap is just this value minus 29, so for the handicap column the script is relatively simple: =IF(D2=0,0,IF(D2>47,18,D2-29)) This ensures that we will not get a negative value for our handicap, and pulls the basic average from the right place. It also sets the handicap to zero if there are no scores present.

Now that we have our spreadsheet back in working order with our new scripts, we are functionally done. We have recreated what my Grandpa’s league uses to generate handicaps.

Comparative Analysis on Data Security and Privacy between Tab XYZ and Tabbycat.

Elections in Belarus attract much attention around the world. The election result is declared as a victory of Mr. Lukashenko with 80% votes. It is interesting to give the simplest statistical analysis of this victory. According to Belarus law, protocols of precinct election commissions (PECs) must be posted up just after the election procedure, so that everybody could take a photograph of the protocols. Currently, 1527 of the 5767 protocols of PECs are available in the open access at https://docs.google.com/spreadsheets/d/17aK3JxBTGtzULB0-YZGOF0hJwhuViHO3/edit#gid=84585767. We focus an attention on two arrays of numbers taken from these photographs. Namely, the number Ni  of voters at some polling station and the number Mi of voters for Mr. Lukashenko at the same polling station. These numbers give a possibility to calculate the average percentage of those who voted for Mr. Lukashenko, which turns out to be about 60%. That is, a random sample approximately of ¼ of total number of protoc...

Stock prices for various companies are obtained from Google Finance through the utilization of the googlefinance() function, and are stored in an .xlsx file format. The stock data is classified and categorized into individual sheets, which correspond to a specific company. The table contains data for each day from the beginning of data collection up to March 2023, including the opening, high, low, and closing prices for the stock, as well as the volume of trades. The prices are denominated in the local currency of the respective country. Drive Stocks file link: https://docs.google.com/spreadsheets/d/1ElCXYXv-NjAmMKy7fQ1bjI05q1xij5hZ2DCLrJs0A5w/edit?usp=share_link

Alongside the stock data, two other files are used: the Inflation consumer prices (annual %) and the Wholesale price index (2010 = 100).

• The Wholesale price index is a measure of the average price of a basket of goods and services in a given economy, including both agricultural and industrial goods at various stages of production and distribution, and may also include import duties. The Laspeyres formula is typically used to calculate the index.

• The Inflation consumer prices (annual %) file measures the annual percentage change in the cost to the average consumer of acquiring a basket of goods and services. The basket may be fixed or changed at specified intervals, such as yearly. The Laspeyres formula is typically used to calculate the index. Both of these files provide valuable context for understanding the performance of the stock market and the broader economic conditions that may be affecting it.

Wholesale price index and Inflation consumer prices are uncleared on propose. The cleaned version of the financial data is also included.

Public google spreadsheet holds the results of a query for an 'enhanced open access publications datset. The tabs show further derivations from this raw data that underpin the visualisations within the paper 'Exploring researcher engagement with open access at Brunel University London using emergent open science infrastructure':https://docs.google.com/spreadsheets/d/1mBnLo_QCKdlFlLwTufINySdN2Euy_CHPBp2GGslaHuQ/edit?usp=sharingObscured or ommited from raw article level dataset - available on request:- Internal record IDs from Symplectic- Journal ISSNs- Citation figures at article level obscured, because some of this data is proprietary. The highest citation figure between Scopus, WoS and Europe PMC is collected at the article level. The average citation was used within the paper and is retained in tab 4.

Context

** One analysis Done in spreadsheets with 202004 and 202005 data **

Content

To adjust for outlier Ride lengths like the max and min below: Max RL =MAX(N:N)978:40:02 minimum RL =MIN(N:N)-0:02:56

TRIMMean to shave off the top and bottom of a dataset. TRIMMEAN =TRIMMEAN(N:N,5%)0:20:20 =TRIMMEAN(N:N,2%)0:21:27

Otherwise the Ride length for 202004 is Average RL 0:35:51

The most common day of the week is Sunday. There are 61,148 members and 23,628 casual riders. mode of DOW 1 CountIf member of MC 61148 CountIf casual of MC 23628

Pivot table 1 2020-04 member_casual AVERAGE of ride_length

Same calculations for 2020-05 Average RL 0:33:23 Max RL 481:36:53 minimum RL -0:01:48 mode of DOW 7 CountIf member of MC 113365 CountIf casual of MC 86909 TRIMMEAN 0:25:22 0:26:59

There are 4 pivot tables included in seperate sheets for other comparisons.

Acknowledgements

I gathered this data using the sources provided by the Google Data Analytics course. All work seen is done by myself.

Inspiration

I want to further use the data in SQL, and Tableau.