Shared public spreadsheet
From Wikipedia, the free encyclopedia
A shared public spreadsheet is a type of spreadsheet that is made available to all or selected users of the spreadsheet for concurrent data entry or modification, usually on a private or public network.
Each authorised user is able to make modifications to the spreadsheet according to their own level of authority. One user may be able to modify any cell in the spreadsheet by changing its value, layout, position or formula whereas other users may be restricted to entering values into one or more cells.
The ability to share a spreadsheet implies a centrally held repository containing the latest modified version which is reminiscent of early online mainframe technology.
Contents |
[edit] History
The first known implementation of a "public" spreadsheet was developed at Imperial Chemical Industries in the UK in the early 1970s and known as "The Works Records system". It enabled data to be entered by shift workers at each chemical plant and the results automatically incorporated into other spreadsheets for senior management by applying formulae linking selected cells from the same or different spreadsheets.
As with the implementation of todays commercial spreadsheets, cells within the spreadsheet could contain data or formulae. A significant feature of this implementation however was the ability to automatically recalculate spreadsheets of a "higher level" on an hourly, daily, weekly, monthly or yearly basis, to produce online displays or printed reports for management. In other words it incorporated a conditional time element as a basis for automatic recalculation.
The system was implemented on an IBM mainframe platform and written entirely in System/360 Assembly Language using CICS transaction processing. Cells could be "named" and could be set anywhere on the screen or report. A numeric cell also had a "units" attribute such as litres, ounces or millimetres which facilitated automatic conversion whenever used in formulae that combined cells of differing units.
The use of a "units" attribute for numeric values additionally enabled automatic detection of illogical operations in formulae such as multiplying feet by feet or litres by inches.
[edit] Reliability
The same system has operated virtually unchanged for 27 years until 2001 and has only recently been superceded!
[edit] Future
With several new product offerings already actively in development, it will only be a matter of time before data will be interchangeable between spreadsheets. Already, users of Editgrid can access Public Stock quotes and currency exchange rates updated every minute. They can also automatically "fetch" the current cell value of another shared public spreadsheet held on the Editgrid servers using a feature known as "Permalink".It may be possible later to fetch cells from other spreadsheets such as Google as they become accessible using API's.
[edit] Potential problems
One potential danger is that a source data item or an intermediate result may be "cross linked" over multiple spreadsheets and re-enter the source spreadsheet causing it to re-calculate indefinitely. This problem can occur as a recursive loop in a single spreadsheet but is potentially more difficult to isolate across multiple spreadsheets. This is simply a new expression of the well known problem of setting up a potential infinite loop, familiar to programmers from the earliest days of computing.
The difference being that in the new scenario of Internet based shared public spreadsheets, the consequences for "wasted" CPU cycles on a global scale is unprecedented.
It is fairly simple to detect bi-directional references between two spreadsheets but somewhat more intensive to detect over possibly hundreds of linked "dependent" spreadsheets. See description of the next potential problem for reasons.
Implementation of a new attribute of spreadsheet cells "refresh rate" will be required to reduce the likelihood of a "never ending calculation cycle" flooding the broadband internet network.
Another potentially related problem is best described using a simple example as there is currently no defined term to describe it. One possible term is a "Shared Public Spreadsheet Cascade" or "RDF Loop" since it could apply to other data exploitation models, not only spreadsheets.
- Consider spreadsheet A (full name "SPS-A") owned by ALICE who provides a data feed into it by entering her value into cell A2 every hour.
- Spreadsheet B is owned by BOB who extracts the current value from SPS-A!A2 and puts it in his own cell A2. As he does so, the current value appears within the cell (taken from Alice's spreadsheet SPS-A).
- Bob likes to combine his results with Alice's so he enters his own input in cell B2 in his spreadsheet. He puts a formula "=A2+B2" in his cell C2. When he has entered the formula, almost immediately the sum appears in his cell C2.
| Alice | Bob | TOTAL |
|---|---|---|
| 10 | 20 | 30 |
- Ideally all of the cells above would be "named" so that if they are later Cut and pasted to a different location on the respective spreadsheets it would not effect references on other "remote" spreadsheets. So, instead of cell A2 in Alice's being referred to as A2 it might be "ALICEval" and Bob's in his B2 as "BOBval". The formula in Bob's C2 (actually named "BOBtot") would therefore be:-
- "=ALICEval+BOBval"
- A third person DAVID has a spreadsheet D and he wishes to display the sum of his value "DAVIDval" and "BOBtot" from BOB'S spreadsheet in one of his cells called "DAVIDtot".
- David has set his refresh rate attribute for "DAVIDtot" to 'every minute' for his combined total. This results in a potential "focus" on Alice's spreadsheet 60 times more often than strictly necessary. Bob's sheet is also re-calculated unnecessarily often as a required intermediate result, forcing a cascade of re-calculations along the line.
[edit] Validation
- This could (in theory) be obviated by validation of refresh rates back up the line to every possible source. However in practise, there could be hundreds, thousands of even hundreds of thousands of source cells scattrered across millions of spreadsheets covering every subject from the price of a Mars bar in New York (sometimes suggested jokingly as better representing true exchange rates) to the pound dollar exchange rate.
[edit] See also
- List of online spreadsheets
- Network effect
- Black box spreadsheet
- Extinct Shared Public Spreadsheet, a formerly available 'SPS' - now extinct.
