Getting Started

Contents

Installation
Excel Interface
Hello World
Calculating Pi
Sample Scripts


Installation

Run the trial to get started with DataNitro.


Excel Interface

When you start Excel, you'll have a new DataNitro tab.

DataNitro gives you access to
    Python through a tab in Excel

The tab has 8 buttons:

Editor
Launches a custom Idle editor. You can use it, or any other editor, to edit Python code. Once you're done, save it as a .py file and run it.
Python Shell
Launches a python shell you can use to interact with the spreadsheet directly.
import
Adds a script to the imported script list.
remove
Removes the script from the list.
Run
Runs the selected script.
Stop
Stops a running script.
Docs
This links to the documentation.
Live Help
This links to our contact information. You can talk with us on any page by clicking "Chat with us!"

Hello World

Hello world with DataNitro is short:

Cell("A1").value = "Hello, World!"
That's it!


Calculating Pi

Let's approximate pi with a Monte Carlo simulation. We'll generate a random 2D point with x and y coordinates between 0 and 1, and see how far it is from the origin; specifically, if it's inside the unit circle. The total area of the points we're choosing from is 1, and the area of the points inside the circle and in our box is pi/4, the fraction of points that fall within our radius should be about pi/4.

We'll use Excel to keep track of our trials, and the variables within each trial. First, let's import random, and label a few cells so we know what's what:

from random import random
Cell("A2").value, Cell("A3").value, Cell("A4").value = "Trial:","Hits:","pi is about:"
Cell("D1").value, Cell("E1").value, Cell("F1").value = "x","y","x^2 + y^2"
Next, we should decide on the number of trials. Let's allow the user to set a number by entering it in the first cell; otherwise, we'll do 100.
trials = Cell("A1").value
if (type(trials)!= int) or (trials < 1):
    trials = 100
Now, we'll run the trials. We'll make a function for this, and keep track of the coordinates of our random points, as well as their distance from the origin, under the headings we made. We'll also keep track of the number of trials.
def pi_calc(n):
    while Cell("B2").value < n:
        x, y = Cell("D2"), Cell("E2")
        r = Cell("F2")
        x.value, y.value = random(), random()
        r.value = x.value**2 + y.value**2
We need to increment the number of trials each time, and also increment "Hits" if our point falls within the circle.
        Cell("B2").value += 1
        if r.value <= 1:
            Cell("B3").value += 1
We can now approximate pi as 4 times the fraction of hits to trials:
        Cell("B4").value = 4*float(Cell("B3").value)/Cell("B2").value
Let's add a small delay at the end of each run so we can watch the function update.
from time import sleep
...
def pi_calc(n):
    ...
    sleep(0.01)
Finally, let's run our function, making sure to reset the number of trials and number of hits beforehand:
Cell("B2").value, Cell("B3").value = 0,0
pi_calc(trials)

We're done! Our finished script:

# Generates approximations of pi.  Put the number of trials you want in A1. More
# trials will result in higher accuracy.

from random import random
from time import sleep

def pi_calc(n):
    while Cell("B2").value < n:
        x, y = Cell("D2"), Cell("E2")
        r = Cell("F2")
        x.value, y.value = random(), random()
        r.value = x.value**2 + y.value**2
        Cell("B2").value += 1
        if r.value <= 1:
            Cell("B3").value += 1
        Cell("B4").value = 4*float(Cell("B3").value)/Cell("B2").value
        sleep(0.01)

trials = Cell("A1").value
if (type(trials)!= int) or (trials < 1):
    trials = 100

# sheet setup
Cell("A2").value, Cell("A3").value, Cell("A4").value = "Trial:","Hits:","pi is about:"
Cell("D1").value, Cell("E1").value, Cell("F1").value = "x","y","x^2 + y^2"
Cell("B2").value, Cell("B3").value = 0,0

pi_calc(trials)
More trials will give greater accuracy - 1000 should be enough to approximate pi to two digits.


Sample Scripts

DataNitro comes with two sample scripts in the example folder: hello_world.py, a hello world program, and pascal_triangle.py, which prints out Pascal's triangle. You can also download and play with pi.py, a spreadsheet and script for binomial option pricing, and the ystockquote script for pulling data from yahoo finance, originally from http://goldb.org/ystockquote.html.

If there are any other examples you'd like to see, or if you come up with one you like, let us know!


If you have any questions, don't hesitate ask - just click "Chat with us!" at the bottom-right.
You may also want to look at the documentation.