working-with-spreadsheets

Name: working-with-spreadsheets
Author: mjunaidca

151installs11forks

How to install working-with-spreadsheets?

mjunaidca/mjs-agent-skills/working-with-spreadsheets

$npx -y skills add mjunaidca/mjs-agent-skills --skill working-with-spreadsheets

Installs into the current project.

Summary

Creates and edits Excel spreadsheets with formulas, formatting, and financial modeling standards. Use when working with .xlsx files, financial models, data analysis, or formula-heavy spreadsheets. Covers formula recalculation, color coding standards, and common pitfalls.

Files · 1

View on GitHub

SKILL.md

1	# Working with Spreadsheets
2
3	## Quick Start
4
5	```python
6	from openpyxl import Workbook
7
8	wb = Workbook()
9	sheet = wb.active
10	sheet['A1'] = 'Revenue'
11	sheet['B1'] = 1000
12	sheet['B2'] = '=B1*1.1' # Use formulas, not hardcoded values!
13	wb.save('output.xlsx')
14	```
15
16	## Critical Rule: Use Formulas, Not Hardcoded Values
17
18	Always use Excel formulas instead of calculating in Python.
19
20	```python
21	# WRONG - Hardcoding calculated values
22	total = df['Sales'].sum()
23	sheet['B10'] = total # Hardcodes 5000
24
25	# CORRECT - Using Excel formulas
26	sheet['B10'] = '=SUM(B2:B9)'
27	```
28
29	## Financial Model Color Coding Standards
30
31	\| Color \| RGB \| Usage \|
32	\|-------\|-----\|-------\|
33	\| Blue text \| 0,0,255 \| Hardcoded inputs, scenario values \|
34	\| Black text \| 0,0,0 \| ALL formulas and calculations \|
35	\| Green text \| 0,128,0 \| Links from other worksheets \|
36	\| Red text \| 255,0,0 \| External links to other files \|
37	\| Yellow background \| 255,255,0 \| Key assumptions needing attention \|
38
39	```python
40	from openpyxl.styles import Font
41
42	# Input cell (user changeable)
43	sheet['B5'].font = Font(color='0000FF') # Blue
44
45	# Formula cell
46	sheet['C5'] = '=B5*1.1'
47	sheet['C5'].font = Font(color='000000') # Black
48
49	# Cross-sheet link
50	sheet['D5'] = "=Sheet2!A1"
51	sheet['D5'].font = Font(color='008000') # Green
52	```
53
54	## Number Formatting Standards
55
56	```python
57	# Currency with thousands separator
58	sheet['B5'].number_format = '$#,##0'
59
60	# Zeros display as dash
61	sheet['B5'].number_format = '$#,##0;($#,##0);-'
62
63	# Percentages with one decimal
64	sheet['C5'].number_format = '0.0%'
65
66	# Valuation multiples
67	sheet['D5'].number_format = '0.0x'
68
69	# Years as text (not 2,024)
70	sheet['A1'] = '2024' # String, not number
71	```
72
73	## Library Selection
74
75	\| Task \| Library \| Example \|
76	\|------\|---------\|---------\|
77	\| Data analysis \| pandas \| `df = pd.read_excel('file.xlsx')` \|
78	\| Formulas & formatting \| openpyxl \| `sheet['A1'] = '=SUM(B:B)'` \|
79	\| Large files (read) \| openpyxl \| `load_workbook('file.xlsx', read_only=True)` \|
80	\| Large files (write) \| openpyxl \| `Workbook(write_only=True)` \|
81
82	## Reading Excel Files
83
84	```python
85	import pandas as pd
86	from openpyxl import load_workbook
87
88	# pandas - data analysis
89	df = pd.read_excel('file.xlsx')
90	all_sheets = pd.read_excel('file.xlsx', sheet_name=None) # Dict of DataFrames
91
92	# openpyxl - preserve formulas
93	wb = load_workbook('file.xlsx')
94	sheet = wb.active
95	print(sheet['A1'].value) # Returns formula string
96
97	# openpyxl - get calculated values (WARNING: loses formulas on save!)
98	wb = load_workbook('file.xlsx', data_only=True)
99	```
100
101	## Creating Excel Files
102
103	```python
104	from openpyxl import Workbook
105	from openpyxl.styles import Font, PatternFill, Alignment
106
107	wb = Workbook()
108	sheet = wb.active
109	sheet.title = 'Model'
110
111	# Headers
112	sheet['A1'] = 'Metric'
113	sheet['B1'] = '2024'
114	sheet['A1'].font = Font(bold=True)
115
116	# Data with formulas
117	sheet['A2'] = 'Revenue'
118	sheet['B2'] = 1000000
119	sheet['B2'].font = Font(color='0000FF') # Blue = input
120
121	sheet['A3'] = 'Growth'
122	sheet['B3'] = '=B2*0.1'
123	sheet['B3'].font = Font(color='000000') # Black = formula
124
125	# Formatting
126	sheet['B2'].number_format = '$#,##0'
127	sheet.column_dimensions['A'].width = 20
128
129	wb.save('model.xlsx')
130	```
131
132	## Editing Existing Files
133
134	```python
135	from openpyxl import load_workbook
136
137	wb = load_workbook('existing.xlsx')
138	sheet = wb['Data'] # Or wb.active
139
140	# Modify cells
141	sheet['A1'] = 'Updated Value'
142	sheet.insert_rows(2)
143	sheet.delete_cols(3)
144
145	# Add new sheet
146	new_sheet = wb.create_sheet('Analysis')
147	new_sheet['A1'] = '=Data!B5' # Cross-sheet reference
148
149	wb.save('modified.xlsx')
150	```
151
152	## Formula Recalculation
153
154	openpyxl writes formulas but doesn't calculate values. Use LibreOffice to recalculate:
155
156	```bash
157	# Recalculate and check for errors
158	python recalc.py output.xlsx
159	```
160
161	The script returns JSON:
162	```json
163	{
164	"status": "success", // or "errors_found"
165	"total_errors": 0,
166	"total_formulas": 42,
167	"error_summary": {
168	"#REF!": {"count": 2, "locations": ["Sheet1!B5", "Sheet1!C10"]}
169	}
170	}
171	```
172
173	## Formula Verification Checklist
174
175	### Before Building
176	- [ ] Test 2-3 sample references first
177	- [ ] Confirm column mapping (column 64 = BL, not BK)
178	- [ ] Remember: DataFrame row 5 = Excel row 6 (1-indexed)
179
180	### Common Pitfalls
181	- [ ] Check for NaN with `pd.notna()` before using values
182	- [ ] FY data often in columns 50+ (far right)
183	- [ ] Search ALL occurrences, not just first match
184	- [ ] Check denominators before division (#DIV/0!)
185	- [ ] Verify cross-sheet references use correct format (`Sheet1!A1`)
186
187	### After Building
188	- [ ] Run `recalc.py` and fix any errors
189	- [ ] Verify #REF!, #DIV/0!, #VALUE!, #NAME? = 0
190
191	## Common Errors
192
193	\| Error \| Cause \| Fix \|
194	\|-------\|-------\|-----\|
195	\| #REF! \| Invalid cell reference \| Check deleted rows/columns \|
196	\| #D

People who liked this also viewed

6 picks

Type

working-with-spreadsheets

151installs11forks

View on GitHub

How to install working-with-spreadsheets?

mjunaidca/mjs-agent-skills/working-with-spreadsheets

$npx -y skills add mjunaidca/mjs-agent-skills --skill working-with-spreadsheets

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Working with Spreadsheets
2
3	## Quick Start
4
5	```python
6	from openpyxl import Workbook
7
8	wb = Workbook()
9	sheet = wb.active
10	sheet['A1'] = 'Revenue'
11	sheet['B1'] = 1000
12	sheet['B2'] = '=B1*1.1' # Use formulas, not hardcoded values!
13	wb.save('output.xlsx')
14	```
15
16	## Critical Rule: Use Formulas, Not Hardcoded Values
17
18	Always use Excel formulas instead of calculating in Python.
19
20	```python
21	# WRONG - Hardcoding calculated values
22	total = df['Sales'].sum()
23	sheet['B10'] = total # Hardcodes 5000
24
25	# CORRECT - Using Excel formulas
26	sheet['B10'] = '=SUM(B2:B9)'
27	```
28
29	## Financial Model Color Coding Standards
30
31	\| Color \| RGB \| Usage \|
32	\|-------\|-----\|-------\|
33	\| Blue text \| 0,0,255 \| Hardcoded inputs, scenario values \|
34	\| Black text \| 0,0,0 \| ALL formulas and calculations \|
35	\| Green text \| 0,128,0 \| Links from other worksheets \|
36	\| Red text \| 255,0,0 \| External links to other files \|
37	\| Yellow background \| 255,255,0 \| Key assumptions needing attention \|
38
39	```python
40	from openpyxl.styles import Font
41
42	# Input cell (user changeable)
43	sheet['B5'].font = Font(color='0000FF') # Blue
44
45	# Formula cell
46	sheet['C5'] = '=B5*1.1'
47	sheet['C5'].font = Font(color='000000') # Black
48
49	# Cross-sheet link
50	sheet['D5'] = "=Sheet2!A1"
51	sheet['D5'].font = Font(color='008000') # Green
52	```
53
54	## Number Formatting Standards
55
56	```python
57	# Currency with thousands separator
58	sheet['B5'].number_format = '$#,##0'
59
60	# Zeros display as dash
61	sheet['B5'].number_format = '$#,##0;($#,##0);-'
62
63	# Percentages with one decimal
64	sheet['C5'].number_format = '0.0%'
65
66	# Valuation multiples
67	sheet['D5'].number_format = '0.0x'
68
69	# Years as text (not 2,024)
70	sheet['A1'] = '2024' # String, not number
71	```
72
73	## Library Selection
74
75	\| Task \| Library \| Example \|
76	\|------\|---------\|---------\|
77	\| Data analysis \| pandas \| `df = pd.read_excel('file.xlsx')` \|
78	\| Formulas & formatting \| openpyxl \| `sheet['A1'] = '=SUM(B:B)'` \|
79	\| Large files (read) \| openpyxl \| `load_workbook('file.xlsx', read_only=True)` \|
80	\| Large files (write) \| openpyxl \| `Workbook(write_only=True)` \|
81
82	## Reading Excel Files
83
84	```python
85	import pandas as pd
86	from openpyxl import load_workbook
87
88	# pandas - data analysis
89	df = pd.read_excel('file.xlsx')
90	all_sheets = pd.read_excel('file.xlsx', sheet_name=None) # Dict of DataFrames
91
92	# openpyxl - preserve formulas
93	wb = load_workbook('file.xlsx')
94	sheet = wb.active
95	print(sheet['A1'].value) # Returns formula string
96
97	# openpyxl - get calculated values (WARNING: loses formulas on save!)
98	wb = load_workbook('file.xlsx', data_only=True)
99	```
100
101	## Creating Excel Files
102
103	```python
104	from openpyxl import Workbook
105	from openpyxl.styles import Font, PatternFill, Alignment
106
107	wb = Workbook()
108	sheet = wb.active
109	sheet.title = 'Model'
110
111	# Headers
112	sheet['A1'] = 'Metric'
113	sheet['B1'] = '2024'
114	sheet['A1'].font = Font(bold=True)
115
116	# Data with formulas
117	sheet['A2'] = 'Revenue'
118	sheet['B2'] = 1000000
119	sheet['B2'].font = Font(color='0000FF') # Blue = input
120
121	sheet['A3'] = 'Growth'
122	sheet['B3'] = '=B2*0.1'
123	sheet['B3'].font = Font(color='000000') # Black = formula
124
125	# Formatting
126	sheet['B2'].number_format = '$#,##0'
127	sheet.column_dimensions['A'].width = 20
128
129	wb.save('model.xlsx')
130	```
131
132	## Editing Existing Files
133
134	```python
135	from openpyxl import load_workbook
136
137	wb = load_workbook('existing.xlsx')
138	sheet = wb['Data'] # Or wb.active
139
140	# Modify cells
141	sheet['A1'] = 'Updated Value'
142	sheet.insert_rows(2)
143	sheet.delete_cols(3)
144
145	# Add new sheet
146	new_sheet = wb.create_sheet('Analysis')
147	new_sheet['A1'] = '=Data!B5' # Cross-sheet reference
148
149	wb.save('modified.xlsx')
150	```
151
152	## Formula Recalculation
153
154	openpyxl writes formulas but doesn't calculate values. Use LibreOffice to recalculate:
155
156	```bash
157	# Recalculate and check for errors
158	python recalc.py output.xlsx
159	```
160
161	The script returns JSON:
162	```json
163	{
164	"status": "success", // or "errors_found"
165	"total_errors": 0,
166	"total_formulas": 42,
167	"error_summary": {
168	"#REF!": {"count": 2, "locations": ["Sheet1!B5", "Sheet1!C10"]}
169	}
170	}
171	```
172
173	## Formula Verification Checklist
174
175	### Before Building
176	- [ ] Test 2-3 sample references first
177	- [ ] Confirm column mapping (column 64 = BL, not BK)
178	- [ ] Remember: DataFrame row 5 = Excel row 6 (1-indexed)
179
180	### Common Pitfalls
181	- [ ] Check for NaN with `pd.notna()` before using values
182	- [ ] FY data often in columns 50+ (far right)
183	- [ ] Search ALL occurrences, not just first match
184	- [ ] Check denominators before division (#DIV/0!)
185	- [ ] Verify cross-sheet references use correct format (`Sheet1!A1`)
186
187	### After Building
188	- [ ] Run `recalc.py` and fix any errors
189	- [ ] Verify #REF!, #DIV/0!, #VALUE!, #NAME? = 0
190
191	## Common Errors
192
193	\| Error \| Cause \| Fix \|
194	\|-------\|-------\|-----\|
195	\| #REF! \| Invalid cell reference \| Check deleted rows/columns \|
196	\| #D

People who liked this also viewed

6 picks

Type