Quadratic 101 | Quadratic - Technical spreadsheet with Python, SQL, and AI.

Writing data to the spreadsheet

Anything you can calculate with code, you can write to the spreadsheet — data types such as strings, numbers, and dates work, as well as types like Dataframes because Pandas, NumPy, and SciPy are included by default — giving you the power to manipulate data in a programming language and visualize it on a spreadsheet.

Code -> Spreadsheet

	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

1# Last line writes to the spreadsheet
22 ** 6

	0	1	2	3
0		likes	dislikes	total
1	Mastodon	81 (20.4%)	12 (3.0%)	93
2	Twitter	142 (35.7%)	41 (10.3%)	183
3	Threads	101 (25.4%)	21 (5.3%)	122
4
5
6
7
8
9
10
11
12
13
14
15

1import pandas as pd
2
3# Imagine some raw data you got somewhere
4data = [
5  { 'source': 'Mastodon', 'likes': 81, 'dislikes': 12 },
6  { 'source': 'Twitter', 'likes': 142, 'dislikes': 41 },
7  { 'source': 'Threads', 'likes': 101, 'dislikes': 21 },
8]
9
10# Turn it into a dataframe
11df = pd.DataFrame(data)
12
13# Add a 'total' column
14df['total'] = df['likes'] + df['dislikes']
15
16# Calculate likes and dislikes as percentages & format
17# e.g. "12" becomes "12 (52%)"
18df['likes'] = df['likes'].apply(
19  lambda x: f'{x} ({x / df["total"].sum() * 100:.1f}%)'
20)
21df['dislikes'] = df['dislikes'].apply(
22  lambda x: f'{x} ({x / df["total"].sum() * 100:.1f}%)'
23)
24
25# Write to the spreadsheet
26df

Read the docs/Open the example file

Reading data from the spreadsheet

Workflows often follow a pattern: take some raw data, transform it, then visualize your work. Quadratic makes this easy by allowing you to programmatically read data from anywhere on your spreadsheet, run some calculations on it, then display the results.

Spreadsheet -> code

	0	1
0	15
1
2	10
3	20	200
4	30	300
5
6
7
8
9
10
11
12
13
14
15

1# Read a value from the sheet
2value = cell(0,0)
3
4# Add a number and write it to the sheet
5value + 5

	0	1	2
0
1		10
2	20		30
3		40
4
5
6
7
8
9
10
11
12
13
14
15

1# Read cell values relative to the current cell's position
2one_left = rel_cell(-1, 0)
3one_right = rel_cell(1, 0)
4one_up = rel_cell(0, -1)
5one_down = rel_cell(0, 1)
6
7# Add them and write them to the sheet
8one_left + one_right + one_up + one_down

	0	1	2	3
0	Bytes		Megabytes	Gigabytes
1	656786	641kB	0.6MB	0.0GB
2	447043536	436,566kB	426.3MB	0.4GB
3	761131472	743,292kB	725.9MB	0.7GB
4	704723912	688,207kB	672.1MB	0.7GB
5	844523850	824,730kB	805.4MB	0.8GB
6	643594786	628,511kB	613.8MB	0.6GB
7	339763942	331,801kB	324.0MB	0.3GB
8	476184258	465,024kB	454.1MB	0.4GB
9
10
11
12
13
14
15

1import pandas as pd
2
3# Get the bytes from the spreadsheet
4values = cells((0,1), (0,100))
5
6# Make a dataframe for the output
7df = pd.DataFrame({
8    'Kilobytes': [],
9    'Megabytes': [],
10    'Gigabytes': []
11})
12
13# Iterate over each by and convert to KB, MB, GB
14for index, row in values.iterrows():
15    byte = int(row[0])
16    
17    # convert to kilobytes and format
18    kb = round(byte / 1024)
19    kb_str = "{:,}kB".format(kb)
20    
21    # convert to megabytes and format
22    mb = round(byte / (1024 * 1024), 1)
23    mb_str = "{:.1f}MB".format(mb)
24
25    # convert to gigabytes and format
26    gb = round(byte / (1024 * 1024 * 1024), 1)
27    gb_str = "{:.1f}GB".format(gb)
28    
29    # append to dataframe
30    df.loc[len(df.index)] = [kb_str, mb_str, gb_str]
31
32df

Read the docs/Open the example file

Code or formulas? How about both

In a traditional spreadsheet you use formulas. In Quadratic you can use both formulas and code, whichever best suits the task at hand. Plus, your no-code teammates can still collaborate with you — it’s the best of both worlds.

Formulas + code

	A	B	C	D	E	F	G	H
0	5
1	5
2
3
4
5
6
7
8
9
10
11
12
13
14
15

1A0 + A1

	A	B	C	D	E	F	G	H
0	1
1	10
2	30
3
4
5
6
7
8
9
10
11
12
13
14
15

1IF(A0 > 5,
2  IF(A1 > 5, 
3    IF(A2 > 5,
4      AVERAGE(A0:A2),
5      'Invalid data'),
6    'Invalid data'),
7  'Invalid data'
8)

Read the docs/Open the example file

Fetch data from any source

Using the primitives of a programming language, you can easily fetch data from an API and work with it in your spreadsheet. Now you can build a live connection to your ever-changing data and your spreadsheet is always up-to-date.

Network

	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14
0	NPM downloads yesterday for: react
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

1# Import library for making network requests
2import requests
3
4# Make API call
5response = requests.get(
6  'https://api.npmjs.org/downloads/range/last-day/react'
7)
8
9# Turn to JSON
10json = response.json()
11
12# Write to the spreadsheet
13json['downloads'][0]['downloads']

	0	1	2
0	NPM downloads
1
2	Package:
3	react
4
5	Timeframe: (choose one in code)
6	last-day	last-week	last-month
7
8	Downloads:
9
10
11
12
13
14
15

1import pandas
2import requests
3
4# Read API parameters from the sheet
5package = cell(0, 3)
6timeframe = cell(2, 6)
7
8# Make API call
9response = requests.get(
10  f'https://api.npmjs.org/downloads/range/{timeframe}/{package}'
11)
12
13# Turn to JSON
14json = response.json()
15
16# Sum total downloads and write to sheet
17sum(item['downloads'] for item in json['downloads'])

	0	1	2	3
0	NPM downloads
1
2	Package:
3	react-router,next,astro
4
5	Timeframe: (choose one in code)
6	last-day	last-week	last-month	last-year
7
8	Package	From	To	Downloads
9		2023-03-28	2024-03-27	527596865
10	next	2023-03-28	2024-03-27	251260122
11	astro	2023-03-28	2024-03-27	7754640
12
13
14
15

1from datetime import datetime, timedelta
2import pandas
3import requests
4
5# Read API parameters from the sheet
6packages_comma_delimited = rel_cell(0, -5)
7timeframe = rel_cell(3, -2)
8
9# Scoped packages, e.g. `@slack/client`, are not yet supported in
10# bulk queries, so we make 1 API call per package
11# https://github.com/npm/registry/blob/master/docs/download-counts.md#per-version-download-counts
12packages = packages_comma_delimited.split(',')
13
14# last-day, last-week, last-month are all keywords in the API
15# but last-year is not, so we'll generate the from/to date range
16# ourselves so it matches the date range format of the API
17# YYYY-MM-DD:YYYY-MM-DD
18if timeframe == 'last-year':
19  today = datetime.today()
20  today_str = today.strftime('%Y-%m-%d')
21  one_year_ago = today - timedelta(days=365)
22  one_year_ago_str = one_year_ago.strftime('%Y-%m-%d')
23  timeframe = f'{one_year_ago_str}:{today_str}'
24
25# Define dataframe with columns
26df = pandas.DataFrame(columns=['Package', 'From', 'To', 'Downloads'])
27
28# Iterate over packages
29for package in packages:
30
31  # Make API call
32  response = requests.get(
33    f'https://api.npmjs.org/downloads/range/{timeframe}/{package}'
34  )
35
36  # Turn to JSON
37  json = response.json()
38
39  # Sum total downloads
40  print(json)
41  total_downloads = sum(
42    item['downloads'] for item in json['downloads']
43  )
44
45  # Append the dataframe
46  df.loc[len(df)] = [package, json['start'], json['end'], total_downloads]
47
48df

Read the docs/Open the example file

Connect to outside data sources

Using native connections, you can write SQL to query your own data sources and bring information into Quadratic, giving you the best of both worlds: the analysis tools of a spreadsheet combined with the structure and storage of a database.

Data connections

	A	B	C	D	E	F	G	H
0
1	Product A
2	Product B
3	Product C
4	Product A
5	Product B
6
7
8
9
10
11
12
13
14
15

1SELECT product_name FROM recent_sales LIMIT 5

	A	B	C	D
0		quantity	price	total
1	Product A	10	29.99	299.9
2	Product B	5	19.99	99.95
3	Product C	2	99.99	199.98
4	Product A	8	29.99	239.92
5	Product B	1	19.99	19.99
6
7
8
9
10
11
12
13
14
15

1SELECT
2    product_name,
3    quantity,
4    price,
5    (quantity * price) AS total
6FROM 
7    recent_sales
8LIMIT
9    5

	A	B	C	D
0		total_quantity	total_sales	last_sale_date
1	Product A	181338	32925	2024-07-15
2	Product B	185624	33627	2024-07-15
3	Product C	185658	33578	2024-07-15
4
5
6
7
8
9
10
11
12
13
14
15

1SELECT
2    product_name,
3    SUM(quantity) AS total_quantity,
4    COUNT(*) AS total_sales,
5    MAX(sale_date) AS last_sale_date
6FROM
7    quadratic_recent_sales
8GROUP BY
9    product_name
10ORDER BY
11    product_name;

Read the docs/Open the example file

Programmatic data visualizations

Using Python, you can leverage the popular graphing library Plotly to create charts and graphs directly in your spreadsheet. Choose from all that plotly has to offer, including scientific charts, 3D graphs, statistical charts, SVG maps, financial charts, and more.

Charts & graphs

	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

1import plotly.express as px
2import pandas as pd
3
4# Create the chart
5fig = px.line(
6  pd.DataFrame({
7    'Years': [2020, 2021, 2022, 2023],
8    'Values': [3, 5, 12, 24]
9  }),
10  x='Years',
11  y='Values',
12  title='Simple line chart'
13)
14
15# Display the chart
16fig.show()

	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

1import plotly.express as px
2
3# Replace this dataframe with your data
4df = px.data.gapminder().query("country=='Canada'")
5
6# Create your chart type, for more chart types:
7# https://plotly.com/python/
8fig = px.line(
9    df,
10    x="year",
11    y="lifeExp",
12    title='Life expectancy in Canada'
13)
14
15# Customize the chart
16fig.add_scatter(
17    x = [fig.data[0].x[-1]],
18    y = [fig.data[0].y[-1]],
19    mode = 'markers + text',
20    marker = {'color':'orange', 'size':10},
21    showlegend = False,
22    text = [fig.data[0].y[-1]],
23    textposition='middle left'
24)
25fig.update_layout(
26    plot_bgcolor="White",
27    width=400,
28    height=280,
29    margin=dict(l=40, r=40, b=40, t=40),
30    yaxis=dict(
31        title=''
32    ),
33    xaxis=dict(
34        title='',
35        showgrid=False,
36        linecolor='rgb(204, 204, 204)',
37        linewidth=2,
38        ticks='outside',
39        tickfont=dict(
40            family='Arial',
41            size=12,
42            color='rgb(82, 82, 82)',
43        ),
44    ),
45)
46
47# Display the chart
48fig.show()

	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

1import urllib.parse
2import plotly.graph_objects as go
3import pandas as pd
4from datetime import datetime
5import requests
6
7# Ticker dates
8ticker = 'AAPL'
9start_date = '2023-01-01'
10end_date = '2023-12-31'
11
12# API parameters
13params = {
14  'adjusted': True,
15  'sort': 'asc',
16  'limit': 120,
17  'apiKey': '<you-api-key-here>'
18}
19query_string = urllib.parse.urlencode(params)
20
21# Query the Polygon API 
22x = requests.get(
23  f'https://api.polygon.io/v2/aggs/ticker/{ticker}/range/1/day/{start_date}/{end_date}?{query_string}'
24)
25
26# Stick json response into DataFrame
27x = x.json()['results']
28df = pd.DataFrame.from_dict(x)
29
30# Convert dates 
31df['t']=(pd.to_datetime(df['t'],unit='ms')) 
32
33# Create chart
34fig = go.Figure(data=[
35  go.Candlestick(x=df['t'],
36  open=df['o'],
37  high=df['h'],
38  low=df['l'],
39  close=df['c'])
40])
41
42# Customize chart appearance
43fig.update_layout(
44  plot_bgcolor="White",
45  margin=dict(l=15, t=50),
46  xaxis_rangeslider_visible=False,
47  width=400, 
48  height=280,
49  title=dict(
50    text=f'AAPL price ({start_date} to {end_date})',
51    font=dict(
52      size=13,
53    )
54  ),
55  xaxis=dict(
56    showline=True,
57    showgrid=False,
58    showticklabels=True,
59    linecolor='rgb(204, 204, 204)',
60    linewidth=2,
61    ticks='outside',
62    tickfont=dict(
63      family='Arial',
64      size=11,
65      color='rgb(82, 82, 82)',
66    ),
67  ),
68  yaxis=dict(
69    tickprefix="$",
70    showticklabels=True,
71  ),
72)
73
74# Display on the spreadsheet
75fig.show()

Read the docs/Open the example file

	0	1	2
0
1	2
2	3
3
4		5	6
5
6		Col 2
7	7	10
8	8	11
9	9	12
10
11
12
13
14
15

Writing data to the spreadsheet

Reading data from the spreadsheet

Code or formulas? How about both

Fetch data from any source

Connect to outside data sources

Programmatic data visualizations

The infinite canvas spreadsheet with code.