← back to Commercialrealestate
CRE: local XLSX exporter (no Drive API) — 5-tab workbook of all brokers/agents/firms/condos/sales for Drive upload
99ec9a73a9dc2ea38f8a301fdf88780da6dde500 · 2026-06-28 15:34:06 -0700 · Steve
Files touched
M package-lock.jsonM package.jsonA scripts/export-to-xlsx.js
Diff
commit 99ec9a73a9dc2ea38f8a301fdf88780da6dde500
Author: Steve <steve@designerwallcoverings.com>
Date: Sun Jun 28 15:34:06 2026 -0700
CRE: local XLSX exporter (no Drive API) — 5-tab workbook of all brokers/agents/firms/condos/sales for Drive upload
---
package-lock.json | 106 +++++++++++++++++++++++++++++++++++++++++++++-
package.json | 3 +-
scripts/export-to-xlsx.js | 55 ++++++++++++++++++++++++
3 files changed, 162 insertions(+), 2 deletions(-)
diff --git a/package-lock.json b/package-lock.json
index 691fbd5..8baed0f 100644
--- a/package-lock.json
+++ b/package-lock.json
@@ -9,7 +9,8 @@
"version": "0.1.0",
"dependencies": {
"express": "^4.22.2",
- "pg": "^8.22.0"
+ "pg": "^8.22.0",
+ "xlsx": "^0.18.5"
}
},
"node_modules/accepts": {
@@ -25,6 +26,15 @@
"node": ">= 0.6"
}
},
+ "node_modules/adler-32": {
+ "version": "1.3.1",
+ "resolved": "https://registry.npmjs.org/adler-32/-/adler-32-1.3.1.tgz",
+ "integrity": "sha512-ynZ4w/nUUv5rrsR8UUGoe1VC9hZj6V5hU9Qw1HlMDJGEJw5S7TfTErWTjMys6M7vr0YWcPqs3qAr4ss0nDfP+A==",
+ "license": "Apache-2.0",
+ "engines": {
+ "node": ">=0.8"
+ }
+ },
"node_modules/array-flatten": {
"version": "1.1.1",
"resolved": "https://registry.npmjs.org/array-flatten/-/array-flatten-1.1.1.tgz",
@@ -93,6 +103,28 @@
"url": "https://github.com/sponsors/ljharb"
}
},
+ "node_modules/cfb": {
+ "version": "1.2.2",
+ "resolved": "https://registry.npmjs.org/cfb/-/cfb-1.2.2.tgz",
+ "integrity": "sha512-KfdUZsSOw19/ObEWasvBP/Ac4reZvAGauZhs6S/gqNhXhI7cKwvlH7ulj+dOEYnca4bm4SGo8C1bTAQvnTjgQA==",
+ "license": "Apache-2.0",
+ "dependencies": {
+ "adler-32": "~1.3.0",
+ "crc-32": "~1.2.0"
+ },
+ "engines": {
+ "node": ">=0.8"
+ }
+ },
+ "node_modules/codepage": {
+ "version": "1.15.0",
+ "resolved": "https://registry.npmjs.org/codepage/-/codepage-1.15.0.tgz",
+ "integrity": "sha512-3g6NUTPd/YtuuGrhMnOMRjFc+LJw/bnMp3+0r/Wcz3IXUuCosKRJvMphm5+Q+bvTVGcJJuRvVLuYba+WojaFaA==",
+ "license": "Apache-2.0",
+ "engines": {
+ "node": ">=0.8"
+ }
+ },
"node_modules/content-disposition": {
"version": "0.5.4",
"resolved": "https://registry.npmjs.org/content-disposition/-/content-disposition-0.5.4.tgz",
@@ -129,6 +161,18 @@
"integrity": "sha512-NXdYc3dLr47pBkpUCHtKSwIOQXLVn8dZEuywboCOJY/osA0wFSLlSawr3KN8qXJEyX66FcONTH8EIlVuK0yyFA==",
"license": "MIT"
},
+ "node_modules/crc-32": {
+ "version": "1.2.2",
+ "resolved": "https://registry.npmjs.org/crc-32/-/crc-32-1.2.2.tgz",
+ "integrity": "sha512-ROmzCKrTnOwybPcJApAA6WBWij23HVfGVNKqqrZpuyZOHqK2CwHSvpGuyt/UNNvaIjEd8X5IFGp4Mh+Ie1IHJQ==",
+ "license": "Apache-2.0",
+ "bin": {
+ "crc32": "bin/crc32.njs"
+ },
+ "engines": {
+ "node": ">=0.8"
+ }
+ },
"node_modules/debug": {
"version": "2.6.9",
"resolved": "https://registry.npmjs.org/debug/-/debug-2.6.9.tgz",
@@ -304,6 +348,15 @@
"node": ">= 0.6"
}
},
+ "node_modules/frac": {
+ "version": "1.1.2",
+ "resolved": "https://registry.npmjs.org/frac/-/frac-1.1.2.tgz",
+ "integrity": "sha512-w/XBfkibaTl3YDqASwfDUqkna4Z2p9cFSr1aHDt0WoMTECnRfBOv2WArlZILlqgWlmdIlALXGpM2AOhEk5W3IA==",
+ "license": "Apache-2.0",
+ "engines": {
+ "node": ">=0.8"
+ }
+ },
"node_modules/fresh": {
"version": "0.5.2",
"resolved": "https://registry.npmjs.org/fresh/-/fresh-0.5.2.tgz",
@@ -903,6 +956,18 @@
"node": ">= 10.x"
}
},
+ "node_modules/ssf": {
+ "version": "0.11.2",
+ "resolved": "https://registry.npmjs.org/ssf/-/ssf-0.11.2.tgz",
+ "integrity": "sha512-+idbmIXoYET47hH+d7dfm2epdOMUDjqcB4648sTZ+t2JwoyBFL/insLfB/racrDmsKB3diwsDA696pZMieAC5g==",
+ "license": "Apache-2.0",
+ "dependencies": {
+ "frac": "~1.1.2"
+ },
+ "engines": {
+ "node": ">=0.8"
+ }
+ },
"node_modules/statuses": {
"version": "2.0.2",
"resolved": "https://registry.npmjs.org/statuses/-/statuses-2.0.2.tgz",
@@ -961,6 +1026,45 @@
"node": ">= 0.8"
}
},
+ "node_modules/wmf": {
+ "version": "1.0.2",
+ "resolved": "https://registry.npmjs.org/wmf/-/wmf-1.0.2.tgz",
+ "integrity": "sha512-/p9K7bEh0Dj6WbXg4JG0xvLQmIadrner1bi45VMJTfnbVHsc7yIajZyoSoK60/dtVBs12Fm6WkUI5/3WAVsNMw==",
+ "license": "Apache-2.0",
+ "engines": {
+ "node": ">=0.8"
+ }
+ },
+ "node_modules/word": {
+ "version": "0.3.0",
+ "resolved": "https://registry.npmjs.org/word/-/word-0.3.0.tgz",
+ "integrity": "sha512-OELeY0Q61OXpdUfTp+oweA/vtLVg5VDOXh+3he3PNzLGG/y0oylSOC1xRVj0+l4vQ3tj/bB1HVHv1ocXkQceFA==",
+ "license": "Apache-2.0",
+ "engines": {
+ "node": ">=0.8"
+ }
+ },
+ "node_modules/xlsx": {
+ "version": "0.18.5",
+ "resolved": "https://registry.npmjs.org/xlsx/-/xlsx-0.18.5.tgz",
+ "integrity": "sha512-dmg3LCjBPHZnQp5/F/+nnTa+miPJxUXB6vtk42YjBBKayDNagxGEeIdWApkYPOf3Z3pm3k62Knjzp7lMeTEtFQ==",
+ "license": "Apache-2.0",
+ "dependencies": {
+ "adler-32": "~1.3.0",
+ "cfb": "~1.2.1",
+ "codepage": "~1.15.0",
+ "crc-32": "~1.2.1",
+ "ssf": "~0.11.2",
+ "wmf": "~1.0.1",
+ "word": "~0.3.0"
+ },
+ "bin": {
+ "xlsx": "bin/xlsx.njs"
+ },
+ "engines": {
+ "node": ">=0.8"
+ }
+ },
"node_modules/xtend": {
"version": "4.0.2",
"resolved": "https://registry.npmjs.org/xtend/-/xtend-4.0.2.tgz",
diff --git a/package.json b/package.json
index bfc6a56..2b71238 100644
--- a/package.json
+++ b/package.json
@@ -14,6 +14,7 @@
},
"dependencies": {
"express": "^4.22.2",
- "pg": "^8.22.0"
+ "pg": "^8.22.0",
+ "xlsx": "^0.18.5"
}
}
diff --git a/scripts/export-to-xlsx.js b/scripts/export-to-xlsx.js
new file mode 100644
index 0000000..f242f77
--- /dev/null
+++ b/scripts/export-to-xlsx.js
@@ -0,0 +1,55 @@
+// export-to-xlsx.js — write the full CRE dataset to a local multi-tab .xlsx (no Drive API / gcloud
+// needed). Steve uploads it to Google Drive → it becomes an editable Google Sheet he owns.
+// Output: /tmp/la-cre-data.xlsx. $0 local. Run: node scripts/export-to-xlsx.js
+'use strict';
+const XLSX = require('xlsx');
+const { Pool } = require('pg');
+const fs = require('fs');
+const path = require('path');
+const pool = new Pool({ host: '/tmp', port: 5432, database: 'cre', user: process.env.USER || 'stevestudio2' });
+const ROOT = path.join(__dirname, '..');
+const OUT = process.env.OUT || '/tmp/la-cre-data.xlsx';
+const d = v => v ? String(v).slice(0, 10) : '';
+
+(async () => {
+ const q = s => pool.query(s).then(r => r.rows);
+ const webCol = (await pool.query(`SELECT 1 FROM information_schema.columns WHERE table_name='broker' AND column_name='website'`)).rowCount ? ', b.website' : ', NULL website';
+
+ const commercial = await q(`SELECT b.name "Broker", f.name "Firm",
+ (SELECT count(*) FROM broker_listing bl WHERE bl.broker_id=b.id)::int "Listings", b.total_assets "Total Crexi Book",
+ b.phone "Phone", b.email "Email" ${webCol.replace('b.website', 'b.website "Website"').replace('NULL website', 'NULL "Website"')}
+ FROM broker b LEFT JOIN firm f ON f.id=b.firm_id WHERE b.agent_type='commercial' OR b.agent_type IS NULL ORDER BY 3 DESC NULLS LAST`);
+ const agents = await q(`SELECT b.name "Agent", f.name "Firm",
+ (SELECT count(*) FROM broker_condo bc WHERE bc.broker_id=b.id)::int "Condos", b.phone "Phone", b.email "Email",
+ ${webCol.includes('b.website') ? 'b.website' : 'NULL'} "Website", b.license "License"
+ FROM broker b LEFT JOIN firm f ON f.id=b.firm_id WHERE b.agent_type='residential' ORDER BY 3 DESC NULLS LAST`).catch(() => []);
+ const firms = await q(`SELECT f.name "Firm", count(DISTINCT bl.listing_id)::int "Listings", count(DISTINCT b.id)::int "Brokers"
+ FROM firm f JOIN broker b ON b.firm_id=f.id JOIN broker_listing bl ON bl.broker_id=b.id GROUP BY f.name ORDER BY 2 DESC`);
+ const sales = await q(`SELECT address "Address", city "City", zip "ZIP", sold_price "Sold Price", sold_date "Sold Date",
+ beds "Beds", baths "Baths", sqft "SqFt" FROM closed_sale WHERE sold_price>0 ORDER BY sold_date DESC NULLS LAST`).catch(() => []);
+ sales.forEach(s => s['Sold Date'] = d(s['Sold Date']));
+
+ let condos = [];
+ try {
+ condos = JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'fha-approved-condos.json'), 'utf8')).condos
+ .filter(c => c.warrant_signal === 'fha_approved')
+ .map(c => ({ Project: c.project_name, City: c.city, ZIP: c.zip, 'Status (proxy)': 'FHA-approved (proxy, not lender-verified)', Expiration: c.expiration_date }));
+ } catch (_) {}
+
+ const wb = XLSX.utils.book_new();
+ const add = (name, rows) => XLSX.utils.book_append_sheet(wb, XLSX.utils.json_to_sheet(rows.length ? rows : [{ note: 'no rows' }]), name.slice(0, 31));
+ add('Commercial Brokers', commercial);
+ add('Residential Agents', agents);
+ add('Firms', firms);
+ add('Warrantable Condos (FHA)', condos);
+ add('Closed Sales (Redfin ~5yr)', sales);
+ XLSX.writeFile(wb, OUT);
+
+ console.log(`wrote ${OUT}`);
+ console.log(` Commercial Brokers: ${commercial.length}`);
+ console.log(` Residential Agents: ${agents.length}`);
+ console.log(` Firms: ${firms.length}`);
+ console.log(` Warrantable Condos: ${condos.length}`);
+ console.log(` Closed Sales: ${sales.length}`);
+ await pool.end();
+})().catch(e => { console.error('ERR', e.message); process.exit(1); });
← 2b99293 CRE: comprehensive 5-tab Google Sheet exporter (commercial b
·
back to Commercialrealestate
·
CRCP: Call Segments panel (Arcstone loan products) — mixed-u f51be7b →