How to use AlphaSheets

alphasheets.md

How to use AlphaSheets

0. Getting data in

There are several ways to get data into AlphaSheets:

• opening a CSV or Excel file
• copying and pasting data from Google Sheets or Excel (this may be a little buggy, so let us know if you encounter issues)
• ripping the web using Python (pandas.io.data.DataReader for example)

1. Running code

Change the language to Python, R, SQL, or Excel

To change the language, simply click the language dropdown in the upper left:


Make sure to put an = in your expression

An = signifies that the expression is a code expression. This is to allow users to type in strings without quotes, just like in Excel.


Code automatically recalculates when dependencies change, just like in Excel


Define global functions in Python or R / opening the global code editor

The global code editor is the home of all globally defined functions and variables. These functions and variables are available in any cell. To open the global code editor, simply click the Code menu, then "Global code editor":



Putting lists and tables in the spreadsheet

When you put a 1D or 2D list, table, or dataframe in the spreadsheet, it will automatically expand to fill more than one cell, as follows:


The following are supported as valid lists and tables:

• Python
  • 1d and 2d lists
  • pandas DataFrame
  • numpy ndarray
• R
  • vectors
  • lists
  • matrices
  • dataframes

Plotting using ggplot or matplotlib

In both Python and R, plots are treated as valid cell outputs. For Python, matplotlib is supported, while for R, ggplot is supported.

Python examples:


In the following example, we are calling the plot() method on a pandas dataframe of stock data:


R example:


Embedding objects in cells

Most Python objects can be embedded in cells. For example, a datetime:


or a dictionary:


or an arbitrary object (shown here in A1):


Viewing objects in cells

When you put an object in a cell,


You can click the object viewer eye icon on the bottom right:


to open the object viewer, which will show you the object in the cell:


Objects supported by the object viewer:

• long strings
• dicts

Viewing errors in cells

Errors output by both Python and R can be viewed using the error pane, which is also available on the bottom right, but will open automatically:

 

Printing from the cell or the global code editor

When you use the Python print keyword in a cell or global code editor, the output will show up in the output pane, which is available on the bottom right:

 

2. Sharing and collaboration

Sharing your sheet

You can get a link to share your sheet simply by clicking the Share menu, then "Share via link."

Making a copy of a sheet

You can clone a sheet simply by clicking File, then Clone. This will produce an exact copy of the sheet.

Making a new sheet

You can make a new sheet by clicking File, then New, or clicking the plus:


and then typing your sheet name.

Dependencies on other sheets

You can make a dependency on another sheet by typing Sheet1!A1 to reference A1 in a sheet named Sheet1.

3. FAQ

How can AlphaSheets be helpful to me?

Three of the main use cases of AlphaSheets are workflow integration, app creation, and collaboration. It's also not surprising for us to see users have more than one of these use cases.

People who value AlphaSheets for workflow integration typically are doing maybe some Python, maybe some R, and some spreadsheet work. They'd prefer to be able to do all of their work in one place. Also, sometimes the intuitive, reactive interface of spreadsheets can appeal to even hardcore data scientists - no more hunting and pecking for columns in a dataframe.

People who value AlphaSheets for app creation want to make data analysis apps for their colleagues without having to build a Javascript/HTML app and backend API from scratch. All they want is an easy GUI with three fields in a form and a chart - shouldn't that be trivial? AlphaSheets makes it trivial - just make a spreadsheet and share it with your colleagues.

People who value AlphaSheets for collaboration are technical users making analyses for non-technical coworkers. AlphaSheets bridges the gap between Python/R users and spreadsheet users, allowing them to collaborate on the same analysis worksheet in real time. Python/R users don't have to painstakingly dump their work into a spreadsheet, while spreadsheet users can update cells and see what happens to the results. Spreadsheet users can even add on to the analysis produced by the Python/R users using only Excel formulas. The more adventurous spreadsheet users can also call functions that their colleagues have defined in Python or R.

How fast is AlphaSheets?

It's fast below 1000 rows and 26 columns. We are currently working on significant performance improvements. The reason it's slow is that performance wasn't a priority while the spreadsheet was still not working.

What Python packages do you have available?

We have all the default ones, plus:

• pandas
• numpy
• scipy
• scikitlearn
• BeautifulSoup

How do I install a Python package?

Right now, it's sort of hacky. Just type =!(sudo pip install ) in any cell in Python mode, and it will install.

How do I install an R package?

Which version of Python do you have?

2.7.6.

Can you put multiple languages in the same spreadsheet?

Yes, you can. You can even pass values back and forth from language to language:

How compatible is AlphaSheets with Excel syntax / expressions?

We support the common Excel functions and all standard Excel syntax (including array expressions, relative $A1 and A$1 references, etc). (In fact, the dollar sign references work in Python and R as well.)

If you'd like us to implement more Excel functions, please let us know - we can get it done within a day.

What formatting options do you have?

Currently, only cell borders, bolding, and italicizing work. You can also use conditional formatting by clicking Edit, then clicking Conditional Formatting:

What keyboard hotkeys do you support?

We support nearly all of the normal Excel hotkeys, including:

• Ctrl-C/V/X (which shift references correctly in all languages)
• Ctrl-Z
• Ctrl-B/I
• Ctrl-D/R
• Ctrl-arrow keys
• Ctrl-Shift-arrow keys
• Ctrl-Shift-$,%, etc

We have our own hotkeys for changing languages:

• Ctrl-1 for Excel
• Ctrl-2 for Python
• Ctrl-3 for R
• Ctrl-4 for SQL

How do I get help, report a bug, or request a feature in AlphaSheets?

There are several options:

• You can click the chat button to the right of AlphaSheets. We try our best to be online 24/7.
• You can join our Slack, where we're always online, and ping us whenever you have a question.
• You can email any of our cofounders: michael, anand, alex, or ritesh (at) alphasheets (dot) com.
• You can call us on the phone. We'd rather not put our phone number on the internet in public, but we'd be happy to give it to you.

4. Advanced features of AlphaSheets

List dependencies

Sometimes you want to reference the entire list at a certain cell, but the list has been auto-expanded. AlphaSheets has something called at-syntax for that: if A1 is a list, you can reference the entire list using @A1:


Multi-line expressions

You can use multi-line Python or R expressions in cells by pressing Ctrl-Enter (or Cmd-Enter if you're on a Mac). If the last line of a cell starts with an = sign, that line will be taken to be the output of the cell. For example:


Monte Carlo

AlphaSheets has special syntax that lets you sample a cell repeatedly.

For example, if I wanted to model the random variable Z = X / Y, where X and Y are normal random variables, I could do this: