The Criteria for Assessing Your Investigation in the AFL

The betting scandals have tainted the image of number of sporting events in all across the globe. Australian Football League (AFL), an annual soccer league held in Australia, has also been alleged with number of betting scandals including allegations of match fixing by players. Recently a betting leader has been arrested in connection with the betting scandals in AFL. A laptop has been recovered from the person arrested in connection with the betting scandal that contains a file of all the data of the matches played since 1897 till 2018. The investigation team has been handed with the original file containing the data and information about all the matches played in chronological order along with the file of the betting leader containing data of these matches in chronological order. The investigator has decided to use the analytical characteristics of excel to conduct a detailed investigation on the data of the both files to identify the examples where changes have been made to the data of AFL matches in the betting master mind’s file by comparing the data with the original data of the matches (Selwyn, Henderson and Chao, 2015).

The period of investigation span over 120 years. The games played between 1897 and the year 2018 are under the scrutiny in the investigation to find out changes made in the excel file of the betting master mind. There have been more than tens of thousands of game that have been played in the AFL over the course of 121 years, i.e. since 1897. It is practically impossible to go through all the games and check scores of team 1 and team 2 manually in the original file and then to compare these with the data in the betting master mind’s laptop (O’Flaherty and Phillips, 2015). Thus, using the “IF” function in excel, a logic has been created to identify matches of which data has been changed in the betting file by comparing the data of the original file.

The five examples of data changes are mentioned below in a screen shot:

Thus, as can be seen that on July 8^th, 2017 there were 5 games that had originally scored differently compared to the score recorded in the file recovered from the master mind of betting syndicate. Along with that the data of a match played on July 12^th, 2018 has also been changed in the file of the mastermind of the betting syndicate as is visible from the above screen shot (Cao, Wang and Li, 2017). It is important to note here that there have been number of games of which data has been changed during the investigation period however, only five examples have been here.

Using Microsoft Excel for Investigating the AFL Scandals

As already mentioned that there have been more than tens of thousands of games played between 1897 and 2018. Since, it is not possible to manually check the data of each of these games an excel sheet has been used to import all the data from the betting mastermind’s file and the original file from the official website of AFL (Zhu, 2014). The data from these two files have been merged in a single file, visible clearly in the following image.

The original score and the score of ringleader have been highlighted in different colours to make sure these are visible clearly without any problem. After merging the data of the ringleader’s file and original file into a separate file, these data have been filtered by using sort and filter function which is located at the far right corner of an excel sheet (Barreto, 2015). The following image shows the sort and filter option in excel sheet:

In the far right corner of the excel sheet the sort and filter option is visible also provided below with bigger image containing only the sort and filter function in excel spreadsheet.

This function helps to sort and filter large number of data on the basis of dates, chronological orders, amount, names and other elements in which the data are segregated into different columns in an excel file (Jacobs, Lyons and Rademacher, 2018).

After adding sorting and filtering option in the spreadsheet, two separate columns have been added immediately beside the betting score and original score for both team 1 and team 2.  “IF” function has been used in these columns, G and J columns respectively with the formula reading, =IF(E3=F3,TRUE,FALSE) (Stout and Schwartz, 2017).

As a result of the above function, the score of betting files shall be compared against the score of original file and the results will be shown in G and J column. G and J column will show “TRUE” if no changes has been made to the data. In case there is changes between the original score and the score as per the betting mastermind’s file then, these columns will highlight “FALSE”. G column is for comparison of score of team 1 and column J is for comparison of score of team 2 (Peltier, 2017). The above formula [=IF(E3=F3,TRUE,FALSE)] has been copied and pasted into all the rows corresponding to all the matches played during 1897 and 2018 to ensure that all the games where data have been changed in the file recovered from the laptop of the master mind is identified. The image below shows six of such matches where the score of both teams have been changed (Sauter, 2015).              

The Ongoing Phenomenon of Betting in the AFL

It is clear from the above investigation that the betting is for real in AFL as there have been number of matches where the original data does not matched with the manipulated data recovered from the laptop of the betting mastermind arrested. The reality of betting fraud is clear from the above investigation (Edlin et. al. 2015). In fact what makes the matter worse is that the changes in data of games is a continuing phenomenon since 1992 and it has been observed in 2018 also. The following images would clear the above observation.

The changes in data in 1992 is visible from the above image as the G column indicates FALSE sing. Further the image below shows the changes made to the data in games played in the year 2018 (Chang and Myers, 2015).

 Thus, from the above it is clear that betting is a continuous phenomenon in AFL and it is still very much present in the matches played as even 2018 match data has also been changed as the manipulated data as per the betting file does not agree with the original score (Bishop, 2017).

References:

Barreto, H., 2015. Why Excel?. The Journal of Economic Education, 46(3), pp.300-309.

Bishop, B.A., 2017, October. Cleaning Data, or More Neat Excel Tricks to Make it Easier. In Charleston Conference: Issues in Book and Serial Acquisition.

Cao, Z., Wang, Y. and Li, D., 2017. Practical reliability analysis of slope stability by advanced Monte Carlo simulations in a spreadsheet. In Probabilistic Approaches for Geotechnical Site Characterization and Slope Stability Analysis (pp. 147-167). Springer, Berlin, Heidelberg.

Chang, K.S.P. and Myers, B.A., 2015, April. A spreadsheet model for handling streaming data. In Proceedings of the 33rd Annual ACM Conference on Human Factors in Computing Systems (pp. 3399-3402). ACM.

Edlin, R., McCabe, C., Hulme, C., Hall, P. and Wright, J., 2015. Building a Markov Cost Effectiveness Model in Excel. In Cost Effectiveness Modelling for Health Technology Assessment (pp. 133-143). Adis, Cham. Available at: https://link.springer.com/chapter/10.1007/978-3-319-15744-3_9 [Accessed on 7 October 2018]

Jacobs, M.A., Lyons, G. and Rademacher, B., 2018. Practice Summary: Using Excel to Build Dedicated Routes from Ad Hoc Routes. Interfaces.

O’Flaherty, J. and Phillips, C., 2015. The use of flipped classrooms in higher education: A scoping review. The internet and higher education, 25, pp.85-95.

Peltier, C., 2017. “What If” analysis: Benefits of utilizing a “What If” analysis in excel. Communications in Statistics-Theory and Methods, 46(12), pp.6119-6129. Available at: https://www.tandfonline.com/doi/abs/10.1080/03610926.2015.1118511 [Accessed on 7 October 2018]

Sauter, V.L., 2015. Making Data Flow Diagrams Accessible for Visually Impaired Students Using Excel Tables. Journal of Information Systems Education, 26(1).

Selwyn, N., Henderson, M. and Chao, S.H., 2015. Exploring the role of digital data in contemporary schools and schooling—‘200,000 lines in an Excel spreadsheet’. British Educational Research Journal, 41(5), pp.767-781.

Stout, D.E. and Schwartz, J.T., 2017. Using Excel 2013 for regression-based cost estimation: Part 2. Management Accounting Quarterly, 18(2).

Zhu, J., 2014. Quantitative models for performance evaluation and benchmarking: data envelopment analysis with spreadsheets (Vol. 213). Springer.