![Page 1: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/1.jpg)
DEPENDENCE TRACING TECHNIQUES FOR SPREADSHEETS: AN INVESTIGATION
SOHON ROYFELIENNE HERMANS
![Page 2: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/2.jpg)
The earth as we see in plain sight
![Page 3: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/3.jpg)
How it really is if we consider flight paths
Interconnected!
![Page 4: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/4.jpg)
Spreadsheet in plain sight
![Page 5: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/5.jpg)
How the cells are interconnected
![Page 6: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/6.jpg)
Why bother about dependence?
“the most important information needs of professional spreadsheet users concern the structure of the formula dependencies” – Hermans et al. in 2011
20% problemsInformal Survey
![Page 7: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/7.jpg)
Necessity is the mother of inventions
But getting your paper accepted and published is the father?!
![Page 8: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/8.jpg)
The gap
![Page 9: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/9.jpg)
Not really the best way
![Page 10: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/10.jpg)
Definitely not the best way
![Page 11: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/11.jpg)
The gap
![Page 12: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/12.jpg)
Hypothesis
Better dependence tracing techniques or tools
developed under scientific research is not adequately
making it across to the industry to benefit real-life
spreadsheet users.
![Page 13: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/13.jpg)
Why the bottleneck between research and industry?
![Page 14: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/14.jpg)
A metrics framework to measure user friendliness and effectiveness?
![Page 15: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/15.jpg)
Any opportunity of improvement in the dependence tracing context?
![Page 16: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/16.jpg)
Review of existing research papers
•Relevance to the topic•Citations and references in
related papers•Distinct and unique
approach• Theory demonstrated
through tool/prototype
Limited scope for this paper
SELECT
![Page 17: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/17.jpg)
The papers
Fluid VisualizationIgarashi et al. 1998
3D Interactive VisualizationShiozawa et al. 1999
Visual Checking with VBAChen et al. 2000
Spreadsheet Visualisation with JavaBallinger et al. 2003
Leveled Dataflow DiagramsHermans et al. 2011
![Page 18: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/18.jpg)
Findings
5 of 5
Innovative Ideas & Tool/Prototypes
![Page 19: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/19.jpg)
Findings
2-Defunct 1-Subject to Compatibility Issue2-Current (Java, C#)
Technology
Defunct Subject to Compatibility Current
![Page 20: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/20.jpg)
Performance Testing
Tested Unrealistic Test Not Tested
Findings
2-Tested 1-Unrealistic Test (400 cells)2-Not Tested
![Page 21: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/21.jpg)
User Testing
User Study No User Study
Findings
1 of 5
![Page 22: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/22.jpg)
Exposure to the Industry
Available to the Industry Never exposed to the Industry
Findings
1 of 5
![Page 23: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/23.jpg)
Why the bottleneck between research and industry?
Lack of motivation?
![Page 24: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/24.jpg)
Why the bottleneck between research and industry?
Difficult to scale up performance?
Difficult to test?
![Page 25: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/25.jpg)
Performance Testing
Tested Unrealistic Test Not Tested
Findings
2-Tested 1-Unrealistic Test (400 cells)2-Not Tested
![Page 26: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/26.jpg)
Why the bottleneck between research and industry?
Difficult to scale up performance?
Difficult to test?
![Page 27: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/27.jpg)
A metrics framework to measure user friendliness and effectiveness?
Apparently none.
![Page 28: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/28.jpg)
User Testing
User Study No User Study
Findings
1 of 5
![Page 29: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/29.jpg)
A metrics framework to measure user friendliness and effectiveness?
To define and measure useability of spreadhsheet
tools.
![Page 30: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/30.jpg)
Any opportunity of improvement in the dependence tracing context?
![Page 32: Dependence tracing techniques for spreadsheets - Sohon Roy at Sems 2014](https://reader036.vdocuments.us/reader036/viewer/2022062709/559258ef1a28ab6f418b4608/html5/thumbnails/32.jpg)
Paper Published In
Technique PrototypePrototype Comments
Industrial Relevance
1.1998 IgarashiFluid Visualization & Animation
Pad++ (now defunct) and Python for UNIX
Performance drastically worsens after 400 cells.No user study.
No implementation plan given. Some ideological influence can be observed in MS Excel 2007 onwards.
2.1999 Shiozawa3D Interactive lift-up operation
OpenGL APIs and SLSC for UNIX
None available. None.
3. 2000 Chen
Visual indications superimposed on Excel sheets like arrows, highlights, shading.
VBA plug-in for Excel.
None available.
No standardized definite influence. However similar techniques are used in ad hoc basis by expert spreadsheet users on individually or internally to organizations.
4.2003 Ballinger
Different types of graphs and trees depicting spreadsheet dependency structure.
Java tool for Excel workbooks.
Successfully tested on a corpus of 259 Excel workbooks of undisclosed source.No user acceptance or comprehension study.
Authors apparently did not carry forward their work on this project.
5.2011 HermansLeveled dataflow diagrams
C# toolkit that accepts Excel workbooks for input.
Extensive user testing with 27 industrial spreadsheet users in a financial asset management company.
Is currently a part of service offered by the company Infotron.nl