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 superseded!
[edit] Current products
- Editgrid is one of the first available commercial products to be released. It emerged from Beta testing in February 2007.
Already with EditGrid there are many shared public spreadsheets freely available for use and in the public domain and many more that are privately shared by the Editgrid community. There is a subscription service for corporate users offering SSL encryption but individual users can register entirely free.
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 "remote data update" using a Permalink to the cell or range of cells.
- see "list of online spreadsheets" for other products still undergoing testing
[edit] Future
With several more new product offerings already actively in development, it will only be a matter of time before data will be interchangeable between spreadsheets on a global level utilizing the internet. Already available in Editgrid, it may be possible later to fetch cells from other spreadsheets such as Google as they become accessible using API's. This emerging technology is likely to follow its predecessor as the next Killer application for the internet.
It is likely that it will soon be possible to use a spreadsheet formula in a cell to lookup a value in an array on a "remote" shared public spreadsheet that is not controlled by the currently active sheet owner. Because of this, spreadsheets will soon gain the new capability of data trawling / data mining and introduces the possibility of say, "Just in time pricing" in a published online price list - based on the actual manufacturing or distribution costs on the day or even on existing sales performance.
[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 or 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] Extinct Shared Public Spreadsheet
An Extinct Shared Public Spreadsheet, is a shared public spreadsheet that once existed but has since been deleted or rendered unusable for one or more of the following reasons:-
- The author, or another user with 'write' authority has deleted it.
- It has ceased to function properly (produces erroneous or spurious results).
- Its credibility has been compromised (see black box spreadsheet).
- It has no further relevance.
[edit] Dilemma
The effect of the removal of a Shared public Spreadsheet from public use is difficult to estimate. The Spreadsheet may have been previously used (and relied upon) by many other "down the line" spreadsheets to provide their input.
The effect is analogous to removing a section of code (a subroutine would be a good example of this) from an application program or other software.
The users of the Shared public spreadsheet would be required to replace the missing or non functional spreadsheet with another, before their spreadsheets would function 'as before'. The reliability of any replacement spreadsheet may initially be in doubt.
[edit] Mirror spreadsheet
As one possible solution to this potential problem, alternative known reliable "mirror" spreadsheets from another source fulfilling the same function (in terms of its input and output cells - at least!) might be defined to act as "backup" for the original SPS. It is possible to envisage competition between commercial spreadsheet vendors supplying functionally equivalent "templates" for such instances. Speculating further, this may lead to competition not just for reliable Shared public spreadsheets but for performance which could be benchmarked continuously by "trawling" through alternative suppliers looking for the fastest, most consistent & reliable results.
[edit] See also
- List of online spreadsheets
- Network effect
- Black box spreadsheet
[edit] Examples of shared public spreadsheets (all external)
[edit] Editgrid
- A List of next 50 years with Leap year indicator and days in each year
- A free formula builder for Editgrid
- population statistics for 2006 from US Census bureau
- budget spreadsheet
- Contact list for USA Homeland security
- Template for Dynamic contact list, automatically updated
