5 Replies - 524 Views - Last Post: 19 November 2017 - 10:34 AM

#1 trent1967c   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 18-November 17

"Theory" Spreadsheet Programmed from Spreadsheet

Posted 18 November 2017 - 11:39 PM

I have a vision for a spreadsheet that is programmable as a spreadsheet itself. (Excel, for example, can be programmed with VBA, but that leaves the spreadsheet environment and idiom, so Excel is not a self-programmable spreadsheet.)

First there one function in a cell or array can reference other cells and arrays, including those that contain functions themselves. Second, IF(boolean,then,else) functions provide for selection.

Third, the Function local scope property for a sheet in a workbook, the PARAMETER() function, and the RETURN() function allow the spreadsheet programmer to write new functions, including recursive functions.

* Is a spreadsheet you can program from the spreadsheet a reasonable goal?
* Has it been done?

I have written a 30 page essay on Scriptsheets, but the essence boils down to the above. (If you, for some inexplicable reason, actually want the draft of the essay I'd be more than happy to share it with the understanding that you would provide feedback). I call the proposal Scriptsheets.

So the plan is to take something like GNUmeric or LibreOffice Calc and graft on a primitive function sheet interpreter.

It would be natural to use C++, but the astute will note that a spreadsheet basically does not rewrite cells (unless you use a circular reference), so I'd also like to use a functional language, maybe Haskel.

In the longer term I'd like as much of the spreadsheet programmable as a spreadsheet to be written to run on the JVM.

As near as I can tell near future Java and typed functional languages, include the following options:

Eta,
Frege,
Kotlin and,
Scala.

Note that a spreadsheet needs to give the satisfaction of immediate results, or failing immediate results, the sensation of actively working, so if the language could be interpreted that would be a huge help.

* Which combination of typed, compiled, interpreted, FOSS functional language that runs on the JVM, JAVA, Haskel, C++, C, used in that order of preference makes the most sense for the Java compatible functional language?

Note also, that I have only the equivalent of an AA degree from a CIS, not a CS, perspective, so the odds are the whole idea is vaporware, unless I can determine feasibility and desirability, then sweet talk real developers to help out.

[email protected] (junk email address)

See also the spreadsheet at http://orgmode.org/

Is This A Good Question/Topic? 0
  • +

Replies To: "Theory" Spreadsheet Programmed from Spreadsheet

#2 andrewsw   User is online

  • Stealth IT
  • member icon

Reputation: 6735
  • View blog
  • Posts: 27,712
  • Joined: 12-December 12

Re: "Theory" Spreadsheet Programmed from Spreadsheet

Posted 19 November 2017 - 12:37 AM

* Is a spreadsheet you can program from the spreadsheet a reasonable goal?
* Has it been done?

I cannot envision what you are describing. Is it something like one of the following?

Excel automation
VSTO

Google Sheets (web based) / Office 365

Excel-DNA
FCell

Or, without the presence of a worksheet or grid, MATLAB.

A key thing for me is whether there is an initial spreadsheet visible/available or not(?).

Excel Macros themselves used to be written in MacroSheet cells.

I am a fan of Excel, and encourage innovative use, but am just not seeing "the vision" from your description.
Was This Post Helpful? 0
  • +
  • -

#3 trent1967c   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 18-November 17

Re: "Theory" Spreadsheet Programmed from Spreadsheet

Posted 19 November 2017 - 03:24 AM

A quick Google did not turn up a good definition of "Excel automation". So I do not know if this is what I am driving at.

It is not VSTO.
It is not a cloud based spreadsheet like Google Sheets or Office 365 Excel, although those can be great.
It is not Excel-DNA, although that looks really cool.
I do not know what FCell is, and a quick Google didn't help.
It is not something like Matlab or Mathematica, to the best of my knowledge.

Imagine that you want to program Excel, from the Excel environment. You don't want to use VBA. That is a switch in environment and in idiom (language, VBA is an imperative programming language, not a spreadsheet.) It is not programming for Excel from C++ or C# in Visual Studio with the .Net libraries and objects.

The idea is to program Excel using Excel proper from the Excel spreadsheet environment.

So you need sequence, selection, and looping. Also, note that a spreadsheet is ALMOST a complete functional programming environment.

For the analog of sequence you have the traversal of the directed acyclic graph to do a calculation iteration on the workbook. In addition, spreadsheet functions or formula can reference other spreadsheet functions or formula.

For selection there is the builtin IF() function and other common spreadsheet functions that can take binary input to produce output.

For looping you could use circular references, but that is problematic.

Instead, I propose we do three things.

1) Define a property of a sheet in the workbook making the sheet a special "function sheet". Function sheets have local scope and do not return their results to the traversal of the DAG until they exit at a RETURN(x) function.
2) We add a PARAMETER(x) function. The PARAMETER function is defined on a function sheet, and calling functions provide arguments for parameters just like for any builtin function.
3) A RETURN(x) function. As you might guess when the traversal of a function, defined in a function sheet encounters a RETURN function, the function (sheet) exits, and the results are returned to the caller.

Note that you can get the same effect as looping with recursion, and I propose that functions defined on function sheets can be recursive.

Now why would you want to do this.

1) It is kind of elegant compared to the way we use add-on languages in spreadsheets now. (And, per this list, it would be a functional programming environment.)

2) There would be the academic reason of doing it to prove it could be done, and maybe get published.

3) More importantly, it could be the basis of a programmable spreadsheet on steroids. It might have specialized applications in science, engineering, and especially economics and finance. (Are you sure you don't want to read my 30 page first draft on Scriptsheets?)

This post has been edited by andrewsw: 19 November 2017 - 03:31 AM
Reason for edit:: Removed previous quote, just press REPLY

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is online

  • Stealth IT
  • member icon

Reputation: 6735
  • View blog
  • Posts: 27,712
  • Joined: 12-December 12

Re: "Theory" Spreadsheet Programmed from Spreadsheet

Posted 19 November 2017 - 03:30 AM

Note that you don't have to quote the previous post in full, there is a Reply button further down the page, and a Fast Reply box.

My Google search of "excel automation" yields this (after the first few ad-links).

I improved the search "fcell" by appending "fcell excel". See tsunami.io.
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is online

  • Stealth IT
  • member icon

Reputation: 6735
  • View blog
  • Posts: 27,712
  • Joined: 12-December 12

Re: "Theory" Spreadsheet Programmed from Spreadsheet

Posted 19 November 2017 - 04:40 AM

I was, though, just responding to "has it been done" with a list that I would have started my investigations with (before writing 30 pages ;))

I'll defer to other contributors. Good luck.
Was This Post Helpful? 0
  • +
  • -

#6 trent1967c   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 18-November 17

Re: "Theory" Spreadsheet Programmed from Spreadsheet

Posted 19 November 2017 - 10:34 AM

Thanks for the input andrewsw. I really appreciate it.

At the risk of beating dead horses, there are a few things going on.

There is the language or languages for implementation used by the developers of the spreadsheet product. There is the language of the spreadsheet itself. There are the programming languages used by the developers of the spreadsheet (say accounting power users), like VBA.

Case A:
So for Excel:

Implementation Language: MS C++ (my guess),
Spreadsheet Language: Excel,
Built-in Language VBA. (Excel HAS a programming language and tool.) FCell may also be along the lines of a built-in (really an extension) language.

Case B:
For Emacs OrgMode spreadsheets (a weird little spreadsheet in a tool whose main purpose is hardly being a spreadsheet.)
(See http://orgmode.org/m...The-spreadsheet )

Implementation Language: Emacs Lisp (my guess),
Spreadsheet languages: Emacs Calc, and Emacs Lisp,
Built-in Language: I'm not sure it has one. Given the fact that you can write ELisp in the cells, it may not need one. However, it also doesn't look like a full-blown 3d spreadsheet.

Case C:
For the proposed Scriptsheets product:

Implementation Languages: Frege (?), Java, Haskell, C++, C, in that order.
Spreadsheet languages: Scriptsheets,
Bulit-in language: Scriptsheets. (Scriptsheets IS a programming language and tool.)

Case X:
You extend the spreadsheet with an implementation language.

Note that a spreadsheet product can have programming languages, it can be a programming language, and it can interpret (or very quickly compile) programming statements in a language such as ELisp or Haskell within the cells of the spreadsheet itself. The features are not mutually exclusive.

ASCII Art Example

NormalSheet
-------------------------
  | A               | B |
-------------------------
1 | 1               |   |
-------------------------
2 | 2               |   |
--------------------------
3 | 3               |   |
--------------------------
4 | 4               |   |
-------------------------
5 | =SUM(A1:A4)     |   | (answer is 10)
-------------------------
6 | =myFunction(A5) |   | (answer is 100)
-------------------------

==================================
A new sheet
==================================

myFunction (it has the "function sheet" property)
----------------------------
  |  A                 | B |
----------------------------
1 | =PARAMETER(double) |   | (10 is passed in)
----------------------------
2 | = A1 * A1          |   | (answer is 100)
----------------------------
3 | =RETURN(A2)        |   | (returns 100)
----------------------------

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1