10 ms·

A Relational Spreadsheet

172 points by dustingetz 4y ago

somat 4y ago
I want the same, I want it so much that I have been working on gasp gui code to do such, an activity I have found I am profoundly bad at. But the theory is, I love the relational database, they are a sort of rigorous superset of the spreadsheet, and I have replaced all my spreadsheets with database tables, however while it is very hard to beat sql for rich comprehensive data transforms and analysis, ad-hoc data entry is very awkward. Most gui tools are focused on database administration and I want one for quick random edits. any hints?
- hermitcrab 4y ago
  Airtable provides an interesting mix of spreadsheet and database functionality.
- Lunrtick 4y ago
  I've tried a bunch, but many of the prettier ones don't have Linux versions. I've been using DBeaver (https://dbeaver.io/ https://dbeaver.io/) for years now though - it's occasionally a little rough around the edges, but overall it's really amazing! It uses jdbc drivers for database support, so it can handle basically anything.
- SloopJon 4y ago
  If you're asking for a GUI for simple CRUD on an arbitrary table, I think that's pretty common. What database are you using, and what API are you using to access it? (Edit: and on what O/S?) If one of the obstacles to data entry is normalization, perhaps an updatable view if your database supports it.
  - deepspace 4y ago
    > I think that's pretty common. Not as common as you might think. It is a hard thing to search for, and the ones I have been able to find all suck. I wrote a PHP application a long time that allowed you to create a GUI for an 'arbitrary' SQL database table, using an XML schema to configure the appearance, validation etc. of the fields. Life got in the way, and I stopped development before it was ready for prime time. Fifteen years later, I went looking for something with similar functionality and was surprised at how few options there still were.
    - masfuerte 4y ago
      A bit more than 20 years ago Microsoft released a desktop product that automated producing a CRUD interface from an XML schema. It looked very nice but it was immediately withdrawn. I was puzzled at the time but I guess now they were threatened with a patent. This might explain why there weren't more products solving this obvious problem. Patents should have expired by now though.
- sublinear 4y ago
  > ad-hoc data entry is very awkward I understand that a spreadsheet "row" often isn't going to be a normalized SQL table row and all that, but isn't mapping the spreadsheet cell ranges into such a schema technically the only problem here? If you had a script that watches your spreadsheet for changes and can drop you into a SQL shell whenever you want, you'd have it both ways.
- hinkley 4y ago
  I wonder if you drag Notebooks a bit back toward spreadsheets if there’s a product there.
- totalhack 4y ago
  Check out NocoDB for an open alternative to AirTable.
- rrrrrrrrrrrryan 4y ago
  Microsoft Access is this. It was quite popular in its heyday, but as time wore on, it turned out that people eventually either want scalable, real, full-blown databases, or infinitely flexible spreadsheets. Anything built with a product in that grey area in between will eventually want to go one way or the other.
  - ghaff 4y ago
    We've seen the same phenomenon in word processing/desktop publishing. We've mostly seen people coalesce around Microsoft Word and its online counterpart/competitors or go full-on desktop publishing with InDesign. People have mostly moved away from lightweight desktop publishing programs for newsletters and the like.
  - zozbot234 4y ago
    Is SQLite a "scalable, real, full-blown database"? That's the scale that Access is going for. (Though it also totally works as a pure frontend to a "real" database connection.)
    - randomdata 4y ago
      SQLite can be used to help build a "scalable, real, full-blown database" application. JET, the SQLite-like engine used by Access, could theoretically be used in the same way. But the topic here is about GUIs which serve the middle-ground between the "scalable, real, full-blown database" applications and the lowly spreadsheet. The SQLite project offers nothing that is comparable to Access.
  - jiggawatts 4y ago
    Access had the capability to use a full-blown SQL Server back end, while retaining 90% of the flexibility on the front end. For example, adding a column in Access would add a column in the database table! We can have our cake and eat it too. The mistake with Access was that instead of keeping a deathgrip on its legacy file-based roots, it ought to have "grown up" and become a web-native HTML5 app that uses SQL Server back-ends as the only option. I still think there's a huge market for something like this.
- erichocean 4y ago
  > I want one for quick random edit. DBeaver fills that need for me.
- burcs 4y ago
  We're building www.outerbase.com to support this use case. We're in the latest YC batch and are hoping to do a launch HN in the near future, but would love to hear your thoughts on what we've built if you are up for checking it out!
- deleted 4y ago
  [deleted]
- kgodey 4y ago
  We are building a open source UI for Postgres DBs that should meet your use case: https://mathesar.org https://mathesar.org. We're doing our first release next week!
  - gnum4n 4y ago
    The UI looks pretty neat, but the install instructions are essentially "curl {url}/install.sh | bash -", which is kind of icky and runs a bunch of sudo commands. Since you're using Docker anyway, why not provide human-readable instructions? Or, alternately, separate the config process from the install process. I'd be much more comfortable running config commands from a script inside the docker container, or as a first-run setup UI, or as a simple .yaml config. It does look like something I'd like to try out later, though. Either after the install process is cleaned up a bit, or once I have time to read through the whole install.sh script. Edit: In case it matters, my personal preference is to install things as linux packages instead of Docker, but I understand that this is a stretch for early-access software.
    - kgodey 4y ago
      This is all good to know, thank you for the feedback! Our current installation process was spun out of our local development setup, which isn't ideal. Cleaning up installation is one of the top things on our to do list. This also includes documentation for setting up Mathesar without Docker. We'd like to do Linux packages as soon as we can too.
- chaps 4y ago
  How large is the data you're working with? Airtable (expensive, with serious row limitations) and its open source alternatives (apparently great, but I've never used one) are basically what you're looking for. They allow you to create relationships pretty trivially. I largely use airtable for collaborative data entry work with 1-10 others, where I need to eventually throw the data into postgres. It works great. Though, it's hard to say where its limitations are until you're a bunch of hours into a project -- some features you'd expect to be easy can be extremely hard, or limited.
- mamcx 4y ago
  I also work in [this space](https://tablam.org https://tablam.org). The tool that was great for this is FoxPro. Is like access, but the genius thing is that it include a super-charged "repl" aka: the command window. It allow to combine GUI + Help + Terminal in one single tool, so you can do use table browse // show a data table go first // you can navigate both by mouse/keyboard and by code!
gregwebs 4y ago
I am loving using coda.io for small data. It's easy to create tables that reference each other. Tables are part of a document and you can write text and make them a little bit interactive with slider controls, etc.
Bloating 4y ago
Grist?
- pcthrowaway 4y ago
  Yeah I was going to say the same. For the unfamiliar it's an open-source collaborative spreadsheet program that can be self-hosted and shared within your org. The sheets themselves are actually sqlite tables, which brings some constraints you might not get with traditional spreadsheet software. I believe it also allows you to run python code for data processing and jobs
- keybits 4y ago
  Grist is outstanding. Deserves much wider recognition as a 'better Airtable' for many use cases: https://www.getgrist.com/ https://www.getgrist.com/
  - mediaman 4y ago
    Completely agreed - I've found it vastly more powerful than other tools, and handles 100k+ rows with speed, even loaded with Python code in each cell. I've been surprised how much I can get done with it, and its ability to display multiple tables on a single page for sequential filtering is powerful.
andylynch 4y ago
Excel’s get and transform makes this dead easy I think?
injidup 4y ago
What about MS Access. I used to use it years ago. It was amazing how simple it was to create and deploy an application based on relational data.
- sorokod 4y ago
  Northwind gives me shivers
- password4321 4y ago
  Also using Access as a front end on top of MSSQL server.
- dottedmag 4y ago
  Very nice piece of software, unfortunately Microsoft didn't port it to Mac.
kfk 4y ago
an interesting approach, how about doing the heavy lifting in an api backend and pushing/pulling data to the sheet using a custom formula? that way you would be able to leverage the full power of a db backend
inglor 4y ago
Excel has get and transform that makes this easy + an odbc provider (you can query excel as an sql database). A reminder for everyone excel web is actually entirely free and you can create stuff with https://excel.new https://excel.new We spent a lot of time making it faster and rewrote a bunch of it ^^ (I'm just an engineer, I don't speak for my corporate overlords)
- KRAKRISMOTT 4y ago
  How's Excel web's feature parity?
- capableweb 4y ago
  excel.new has a "weird" TLD, no mention of Microsoft in the domain and asks me to auth with my Microsoft account before I can even see the site. If I came across this in the wild, I'd for sure think this is some phishing or generally bad website.
  - switch007 4y ago
    Agreed. That’s bad even for Microsoft. What is wrong with excel.office.com with a version toggle?
    - arjvik 4y ago
      excel.new is a shortcut to creating a new spreadsheet, not a brand new version of Excel
      - switch007 4y ago
        excel.office.com/new then
  - cldellow 4y ago
    Those are all valid points, of course, and you can navigate to excel.office.com if you don't trust the OP (although it will also immediately redirect you to an auth window) The .new TLD is pretty nifty. sheets.new gets you the same thing as excel.new, but for Google.
notpushkin 4y ago
The UI is kinda nice. I'd love something like that for plain old SQLite.
chroteus 4y ago
Just throwing it around, but there is Apache Calcite, a DBMS without the Database part. It's an SQL parsing, logical & physical plan building framework. It's pretty much a DBMS without any database of its own. You can program up an adapter for "whatever you want" pretty much and query "whatever you want" with SQL.
plaidfuji 4y ago
Bigquery is so deeply integrated with Sheets these days that you can establish live links from specified tabs to BQ (applying a SQL schema in transit), and vice versa, such that you can build a SQL layer into your spreadsheet “backend”. Bigquery is so cheap on data of that size that you’ll never realize significant cost, and provides a great query builder GUI on top.
anigbrowl 4y ago
[Laughs in pandas] I don't want to be dismissive, this is nice work and it's clean and lightweight. But it might be good to look at existing solutions in this area - pandas was developed within the financial industry to solve exactly this sort of issue. If you need more topological flexibility there is xarray, and if you need spreadsheet type immediacy it's worth looking into Mito. Rustaceans should look into pola.rs: https://github.com/pola-rs/polars https://github.com/pola-rs/polars
- chaps 4y ago
  [Sobs in hours spent in pandas documentation] Or, just use a relational database. Alternatively, airtable and similar are basically relational databases that have some really neat features that let you create relationships by just copying/pasting data, or importing CSVs. They're limited in a lot of ways, but it solves a certain set of problems that can't be solved with code or excel.
  - anigbrowl 4y ago
    I went through that as well; Wes McKinney is super-smart, but not the best teacher, and unfortunately his style of example has become a norm ion Stack Overflow etc. import pandas as pd df = pd.whaaargarrrrbllllll[(['what']['the']['fuck'), is.this['shit'], I, mean, seriously] (outputs) df[.astype('int64').fillna('spork') df.groupby[['uppers']['downers']['all arounders']].join(inner, child, trauma, (yes && no)) df['confused'].very(simple['example']) # the thing being explained I'm exaggerating, but not by much. Most examples in documentation or McKinney's tutorial work is presented as a complete small program in a REPL, and while that does make it easy to follow along by imitation, learning pandas feels like a painfully fragmented process at first. Also, tehre's a widespread assumption among pandas experts that people coming to pandas are already familiar with SQL, even more than Python in fact. I'm sure this reflects the initial user base and to bfair it's probably a true assumption for a lot of folk. But if you came from a more CS or scientific context rather than a database one, it's anotehr avoidable layer of confusion. I can't recommend a book unfortunately - I just worked with McKinney's own materials and suffered for a while until things started to click. Once I realized what I found frustrating about the tutorial materials I began to realize that I could read it more selectively - and also that the code base is in constant flux. There are often 2 or 3 different ways to do the same thing, with different approaches being deprecated or promoted over time.
gigatexal 4y ago
“ In trying to tidy up my finances I had to rebalance my portfolio, but even though I had all of my trades loaded into a spreadsheet, I found it difficult to aggregate them by ticker / account / category and calculate the actionable bit — the stocks and quantities I needed to buy/sell to meet a desired allocation.” Is this not what a pivot table does?
- deleted 4y ago
  [deleted]
- emeril 4y ago
  that's exactly what a pivot table does
franciscop 4y ago
Ah nice memories, one of my first open source projects was to take a Google Sheet and convert it into a lightweight "database", basically parse the data and parse it into a big array of JSON data that could be used in any website: const data = await driveDb('sheet-id'); console.log(data); // [{ name: 'John', age: 31 }, { name: 'Sarah', age: 27 }, ...] https://github.com/franciscop/drive-db/ https://github.com/franciscop/drive-db/ It was born very similarly to how the article describes it. For low-amount of data, a spreadsheet is IMHO a much better low-tech collaborative tool than a database.
webdoodle 4y ago
At my previous job, one of the accountants asked me on my first week if I could solve a spreadsheet problem they had involving payroll for it's ~250 employee's. As a 20 year database developer, manager and analyst, I was really hesitant, but after she showed me the amount of time it was taking her to do this every 2 weeks, I sort of felt obliged. All of the data was from several flat files, it consisted of each employee's hours for the period, accrued sick time, vacation time, comp time, etc. She had to manually copy and paste this data into Excel, fix formatting issues, then use it to calculate several totals. It was then fed into another system (that would only accept Excel files for input), that would print the pay checks with all this data summarized. This was clearly critical for the organization, and it was taking her a couple days to do manually. I begged her, and my supervisor, to let me do the whole thing in SQL Server or MySQL, but she wouldn't have it, because it HAD to be something she could adjust manually. I explained I could make it look and feel like Excel, but she wouldn't have it. I realize to anyone in databases (including me), I should have fought harder to do it right. However, as a new employee it was more important to gain trust than show off new technologies. So I compromised. I setup a system that imported all of the data into SQL Server, cleaned it up, then exported it to Excel. The tricky part was working with VBA, and creating recursive expressions in Excel during the export. It took me about a week of time, mostly because I hadn't used VBA in a decade. The end product, from her point of view, looked exactly like her original spreadsheet. She could then still manually fix any issues that came about before importing it into the paycheck printer. In the end, it saved her 2 days a week, or 8 days a month or 96 days a year. I became her new best friend. I was always the first to get my paycheck, and never had any issues when I needed support from finance. Probably the smartest move I made early in that job. ;)
- emeril 4y ago
  fwiw, one can import the contents of a list of excel files into one big excel file using some relatively vanilla vba without having to involve sql I personally usually only involve a database if there's some significant amount of data involved and/or if it's significantly more efficient than the above I don't get the hate on excel in this thread compared to rdbms's; i.e., just use a few vlookups for some joins which are usually sufficiently performant especially if you don't have that much data in which case you shouldn't be using excel in the first place... (though, if you're "clever" and want higher performance vlookups, just use it's often maligned indexed lookup feature more carefully to allow for both exact and indexed lookups which can be like >100x faster...)
fatih-erikli 4y ago
The category names are replicated for the each recurring symbol, am I right? I thought you're gonna make it by giving them category IDs as we do in relational databases.
adius 4y ago
I’m currently working on https://www.airsequel.com https://www.airsequel.com, which includes a spreadsheet interface for SQLite databases. By now I use it for most stuff I would have used Excel/Apple Numbers before.

172 points by dustingetz 4y ago

I want the same, I want it so much that I have been working on gasp gui code to do such, an activity I have found I am profoundly bad at. But the theory is, I love the relational database, they are a sort of rigorous superset of the spreadsheet, and I have replaced all my spreadsheets with database tables, however while it is very hard to beat sql for rich comprehensive data transforms and analysis, ad-hoc data entry is very awkward. Most gui tools are focused on database administration and I want one for quick random edits. any hints?

hermitcrab 4y ago
Airtable provides an interesting mix of spreadsheet and database functionality.
Lunrtick 4y ago
I've tried a bunch, but many of the prettier ones don't have Linux versions. I've been using DBeaver (https://dbeaver.io/ https://dbeaver.io/) for years now though - it's occasionally a little rough around the edges, but overall it's really amazing! It uses jdbc drivers for database support, so it can handle basically anything.
SloopJon 4y ago
If you're asking for a GUI for simple CRUD on an arbitrary table, I think that's pretty common. What database are you using, and what API are you using to access it? (Edit: and on what O/S?) If one of the obstacles to data entry is normalization, perhaps an updatable view if your database supports it.
- deepspace 4y ago
  > I think that's pretty common. Not as common as you might think. It is a hard thing to search for, and the ones I have been able to find all suck. I wrote a PHP application a long time that allowed you to create a GUI for an 'arbitrary' SQL database table, using an XML schema to configure the appearance, validation etc. of the fields. Life got in the way, and I stopped development before it was ready for prime time. Fifteen years later, I went looking for something with similar functionality and was surprised at how few options there still were.
  - masfuerte 4y ago
    A bit more than 20 years ago Microsoft released a desktop product that automated producing a CRUD interface from an XML schema. It looked very nice but it was immediately withdrawn. I was puzzled at the time but I guess now they were threatened with a patent. This might explain why there weren't more products solving this obvious problem. Patents should have expired by now though.
sublinear 4y ago
> ad-hoc data entry is very awkward I understand that a spreadsheet "row" often isn't going to be a normalized SQL table row and all that, but isn't mapping the spreadsheet cell ranges into such a schema technically the only problem here? If you had a script that watches your spreadsheet for changes and can drop you into a SQL shell whenever you want, you'd have it both ways.
hinkley 4y ago
I wonder if you drag Notebooks a bit back toward spreadsheets if there’s a product there.
totalhack 4y ago
Check out NocoDB for an open alternative to AirTable.
rrrrrrrrrrrryan 4y ago
Microsoft Access is this. It was quite popular in its heyday, but as time wore on, it turned out that people eventually either want scalable, real, full-blown databases, or infinitely flexible spreadsheets. Anything built with a product in that grey area in between will eventually want to go one way or the other.
- ghaff 4y ago
  We've seen the same phenomenon in word processing/desktop publishing. We've mostly seen people coalesce around Microsoft Word and its online counterpart/competitors or go full-on desktop publishing with InDesign. People have mostly moved away from lightweight desktop publishing programs for newsletters and the like.
- zozbot234 4y ago
  Is SQLite a "scalable, real, full-blown database"? That's the scale that Access is going for. (Though it also totally works as a pure frontend to a "real" database connection.)
  - randomdata 4y ago
    SQLite can be used to help build a "scalable, real, full-blown database" application. JET, the SQLite-like engine used by Access, could theoretically be used in the same way. But the topic here is about GUIs which serve the middle-ground between the "scalable, real, full-blown database" applications and the lowly spreadsheet. The SQLite project offers nothing that is comparable to Access.
- jiggawatts 4y ago
  Access had the capability to use a full-blown SQL Server back end, while retaining 90% of the flexibility on the front end. For example, adding a column in Access would add a column in the database table! We can have our cake and eat it too. The mistake with Access was that instead of keeping a deathgrip on its legacy file-based roots, it ought to have "grown up" and become a web-native HTML5 app that uses SQL Server back-ends as the only option. I still think there's a huge market for something like this.
erichocean 4y ago
> I want one for quick random edit. DBeaver fills that need for me.
burcs 4y ago
We're building www.outerbase.com to support this use case. We're in the latest YC batch and are hoping to do a launch HN in the near future, but would love to hear your thoughts on what we've built if you are up for checking it out!
deleted 4y ago
[deleted]
kgodey 4y ago
We are building a open source UI for Postgres DBs that should meet your use case: https://mathesar.org https://mathesar.org. We're doing our first release next week!
- gnum4n 4y ago
  The UI looks pretty neat, but the install instructions are essentially "curl {url}/install.sh | bash -", which is kind of icky and runs a bunch of sudo commands. Since you're using Docker anyway, why not provide human-readable instructions? Or, alternately, separate the config process from the install process. I'd be much more comfortable running config commands from a script inside the docker container, or as a first-run setup UI, or as a simple .yaml config. It does look like something I'd like to try out later, though. Either after the install process is cleaned up a bit, or once I have time to read through the whole install.sh script. Edit: In case it matters, my personal preference is to install things as linux packages instead of Docker, but I understand that this is a stretch for early-access software.
  - kgodey 4y ago
    This is all good to know, thank you for the feedback! Our current installation process was spun out of our local development setup, which isn't ideal. Cleaning up installation is one of the top things on our to do list. This also includes documentation for setting up Mathesar without Docker. We'd like to do Linux packages as soon as we can too.
chaps 4y ago
How large is the data you're working with? Airtable (expensive, with serious row limitations) and its open source alternatives (apparently great, but I've never used one) are basically what you're looking for. They allow you to create relationships pretty trivially. I largely use airtable for collaborative data entry work with 1-10 others, where I need to eventually throw the data into postgres. It works great. Though, it's hard to say where its limitations are until you're a bunch of hours into a project -- some features you'd expect to be easy can be extremely hard, or limited.
mamcx 4y ago
I also work in [this space](https://tablam.org https://tablam.org). The tool that was great for this is FoxPro. Is like access, but the genius thing is that it include a super-charged "repl" aka: the command window. It allow to combine GUI + Help + Terminal in one single tool, so you can do use table browse // show a data table go first // you can navigate both by mouse/keyboard and by code!

gregwebs 4y ago

I am loving using coda.io for small data. It's easy to create tables that reference each other. Tables are part of a document and you can write text and make them a little bit interactive with slider controls, etc.

Bloating 4y ago

Grist?

pcthrowaway 4y ago
Yeah I was going to say the same. For the unfamiliar it's an open-source collaborative spreadsheet program that can be self-hosted and shared within your org. The sheets themselves are actually sqlite tables, which brings some constraints you might not get with traditional spreadsheet software. I believe it also allows you to run python code for data processing and jobs
keybits 4y ago
Grist is outstanding. Deserves much wider recognition as a 'better Airtable' for many use cases: https://www.getgrist.com/ https://www.getgrist.com/
- mediaman 4y ago
  Completely agreed - I've found it vastly more powerful than other tools, and handles 100k+ rows with speed, even loaded with Python code in each cell. I've been surprised how much I can get done with it, and its ability to display multiple tables on a single page for sequential filtering is powerful.

andylynch 4y ago

Excel’s get and transform makes this dead easy I think?

injidup 4y ago

What about MS Access. I used to use it years ago. It was amazing how simple it was to create and deploy an application based on relational data.

sorokod 4y ago
Northwind gives me shivers
password4321 4y ago
Also using Access as a front end on top of MSSQL server.
dottedmag 4y ago
Very nice piece of software, unfortunately Microsoft didn't port it to Mac.

kfk 4y ago

an interesting approach, how about doing the heavy lifting in an api backend and pushing/pulling data to the sheet using a custom formula? that way you would be able to leverage the full power of a db backend

inglor 4y ago

Excel has get and transform that makes this easy + an odbc provider (you can query excel as an sql database). A reminder for everyone excel web is actually entirely free and you can create stuff with https://excel.new https://excel.new We spent a lot of time making it faster and rewrote a bunch of it ^^ (I'm just an engineer, I don't speak for my corporate overlords)

KRAKRISMOTT 4y ago
How's Excel web's feature parity?
capableweb 4y ago
excel.new has a "weird" TLD, no mention of Microsoft in the domain and asks me to auth with my Microsoft account before I can even see the site. If I came across this in the wild, I'd for sure think this is some phishing or generally bad website.
- switch007 4y ago
  Agreed. That’s bad even for Microsoft. What is wrong with excel.office.com with a version toggle?
  - arjvik 4y ago
    excel.new is a shortcut to creating a new spreadsheet, not a brand new version of Excel
    - switch007 4y ago
      excel.office.com/new then
- cldellow 4y ago
  Those are all valid points, of course, and you can navigate to excel.office.com if you don't trust the OP (although it will also immediately redirect you to an auth window) The .new TLD is pretty nifty. sheets.new gets you the same thing as excel.new, but for Google.

notpushkin 4y ago

The UI is kinda nice. I'd love something like that for plain old SQLite.

chroteus 4y ago

Just throwing it around, but there is Apache Calcite, a DBMS without the Database part. It's an SQL parsing, logical & physical plan building framework. It's pretty much a DBMS without any database of its own. You can program up an adapter for "whatever you want" pretty much and query "whatever you want" with SQL.

plaidfuji 4y ago

Bigquery is so deeply integrated with Sheets these days that you can establish live links from specified tabs to BQ (applying a SQL schema in transit), and vice versa, such that you can build a SQL layer into your spreadsheet “backend”. Bigquery is so cheap on data of that size that you’ll never realize significant cost, and provides a great query builder GUI on top.

anigbrowl 4y ago

[Laughs in pandas] I don't want to be dismissive, this is nice work and it's clean and lightweight. But it might be good to look at existing solutions in this area - pandas was developed within the financial industry to solve exactly this sort of issue. If you need more topological flexibility there is xarray, and if you need spreadsheet type immediacy it's worth looking into Mito. Rustaceans should look into pola.rs: https://github.com/pola-rs/polars https://github.com/pola-rs/polars

chaps 4y ago
[Sobs in hours spent in pandas documentation] Or, just use a relational database. Alternatively, airtable and similar are basically relational databases that have some really neat features that let you create relationships by just copying/pasting data, or importing CSVs. They're limited in a lot of ways, but it solves a certain set of problems that can't be solved with code or excel.
- anigbrowl 4y ago
  I went through that as well; Wes McKinney is super-smart, but not the best teacher, and unfortunately his style of example has become a norm ion Stack Overflow etc. import pandas as pd df = pd.whaaargarrrrbllllll[(['what']['the']['fuck'), is.this['shit'], I, mean, seriously] (outputs) df[.astype('int64').fillna('spork') df.groupby[['uppers']['downers']['all arounders']].join(inner, child, trauma, (yes && no)) df['confused'].very(simple['example']) # the thing being explained I'm exaggerating, but not by much. Most examples in documentation or McKinney's tutorial work is presented as a complete small program in a REPL, and while that does make it easy to follow along by imitation, learning pandas feels like a painfully fragmented process at first. Also, tehre's a widespread assumption among pandas experts that people coming to pandas are already familiar with SQL, even more than Python in fact. I'm sure this reflects the initial user base and to bfair it's probably a true assumption for a lot of folk. But if you came from a more CS or scientific context rather than a database one, it's anotehr avoidable layer of confusion. I can't recommend a book unfortunately - I just worked with McKinney's own materials and suffered for a while until things started to click. Once I realized what I found frustrating about the tutorial materials I began to realize that I could read it more selectively - and also that the code base is in constant flux. There are often 2 or 3 different ways to do the same thing, with different approaches being deprecated or promoted over time.

gigatexal 4y ago

“ In trying to tidy up my finances I had to rebalance my portfolio, but even though I had all of my trades loaded into a spreadsheet, I found it difficult to aggregate them by ticker / account / category and calculate the actionable bit — the stocks and quantities I needed to buy/sell to meet a desired allocation.” Is this not what a pivot table does?

deleted 4y ago
[deleted]
emeril 4y ago
that's exactly what a pivot table does

franciscop 4y ago

Ah nice memories, one of my first open source projects was to take a Google Sheet and convert it into a lightweight "database", basically parse the data and parse it into a big array of JSON data that could be used in any website: const data = await driveDb('sheet-id'); console.log(data); // [{ name: 'John', age: 31 }, { name: 'Sarah', age: 27 }, ...] https://github.com/franciscop/drive-db/ https://github.com/franciscop/drive-db/ It was born very similarly to how the article describes it. For low-amount of data, a spreadsheet is IMHO a much better low-tech collaborative tool than a database.

webdoodle 4y ago

At my previous job, one of the accountants asked me on my first week if I could solve a spreadsheet problem they had involving payroll for it's ~250 employee's. As a 20 year database developer, manager and analyst, I was really hesitant, but after she showed me the amount of time it was taking her to do this every 2 weeks, I sort of felt obliged. All of the data was from several flat files, it consisted of each employee's hours for the period, accrued sick time, vacation time, comp time, etc. She had to manually copy and paste this data into Excel, fix formatting issues, then use it to calculate several totals. It was then fed into another system (that would only accept Excel files for input), that would print the pay checks with all this data summarized. This was clearly critical for the organization, and it was taking her a couple days to do manually. I begged her, and my supervisor, to let me do the whole thing in SQL Server or MySQL, but she wouldn't have it, because it HAD to be something she could adjust manually. I explained I could make it look and feel like Excel, but she wouldn't have it. I realize to anyone in databases (including me), I should have fought harder to do it right. However, as a new employee it was more important to gain trust than show off new technologies. So I compromised. I setup a system that imported all of the data into SQL Server, cleaned it up, then exported it to Excel. The tricky part was working with VBA, and creating recursive expressions in Excel during the export. It took me about a week of time, mostly because I hadn't used VBA in a decade. The end product, from her point of view, looked exactly like her original spreadsheet. She could then still manually fix any issues that came about before importing it into the paycheck printer. In the end, it saved her 2 days a week, or 8 days a month or 96 days a year. I became her new best friend. I was always the first to get my paycheck, and never had any issues when I needed support from finance. Probably the smartest move I made early in that job. ;)

emeril 4y ago
fwiw, one can import the contents of a list of excel files into one big excel file using some relatively vanilla vba without having to involve sql I personally usually only involve a database if there's some significant amount of data involved and/or if it's significantly more efficient than the above I don't get the hate on excel in this thread compared to rdbms's; i.e., just use a few vlookups for some joins which are usually sufficiently performant especially if you don't have that much data in which case you shouldn't be using excel in the first place... (though, if you're "clever" and want higher performance vlookups, just use it's often maligned indexed lookup feature more carefully to allow for both exact and indexed lookups which can be like >100x faster...)

fatih-erikli 4y ago

The category names are replicated for the each recurring symbol, am I right? I thought you're gonna make it by giving them category IDs as we do in relational databases.

adius 4y ago

I’m currently working on https://www.airsequel.com https://www.airsequel.com, which includes a spreadsheet interface for SQLite databases. By now I use it for most stuff I would have used Excel/Apple Numbers before.