Multiple dynamic data filters

English
#1

Hi,

I’ve created a workbook for tracking stock portfolios. On one sheet I have a transaction list (Transactions) containing the stock ticker, date of transaction, shares, share price, and cost. This makes it very simple to take a digital history from a financial institution and import into the spreadsheet. On another sheet I have each individual stock with the transactions for that stock only (Stock History). I also do some calculations on the data for each individual stock.

So I currently have this functioning by using an array formula that scans the entire list of transactions and when it finds a row matching the stock at the top it copies it down and moves on. There are a couple of issues with this. First, the array formula goes through the entire list of transactions for every single stock. For a small portfolio, this is not a problem. However, I’m working on one with over 2000 transactions and counting with 65 holdings so the array formula takes a significant amount of time to update (more than 5 minutes). The second issue is that in the individual stock sheet where the array formulas are running, there are only 10 rows of data every time the sheet is re-opened. Because of the update time, I’ve been saving the file with autocalc > off so the file can be opened without waiting for it to update. However, all of my calculations on the data are wrong because data is missing so I have to turn on autocalc then do a recalculate hard command to get it to update the arrays.

I looked into using the advanced filter with copy function which I can do and then do a data > refresh range to update, but it is only good for a single filter as far as I can figure out. If I can use the advanced filter for 65 filters on the same data and be able to do the refresh option when new transactions are added that seems like it would be ideal.

Is there a better way to do this?

Here is the formula I use to “filter” the stocks:
=IFERROR(INDEX($Transactions.$B$5:$Transactions.$E2110,SMALL(IF($Transactions.$A$5:$Transactions.$A$2110=JS$1,ROW($Transactions.$A$5:$Transactions.$A$2110)-MIN(ROW($Transactions.$A$5:$Transactions.$A$2110))+1),ROW($Transactions.$B$5:$Transactions.$E$2110)-MIN(ROW($Transactions.$B$5:$Transactions.$EE2110))+1),COLUMN($Transactions.$B$5:$Transactions.$E$2110)-MIN(COLUMN($Transactions.$B$5:$Transactions.$E$2110))+1),"")

Here is an image with the transaction list on the right and the filtered data on the left. Sorry I can’t upload more than 1 image due to new user restrictions.

image
image1082×950 301 KB

Version: 7.3.7.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 4; OS: Linux 6.2; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.7-0ubuntu0.22.04.3
Calc: threaded

Thanks,
Paul

#2

I’m using a table, not a formula to filter. So I write the job-code (stock-id in your case) in rows and the filter selects all the rows with the same ids from the master-list. (Can also be done with a simple sql SELECT when data resides in a database).
.
I can’t post an example from my mobile - maybe later…
Check the help and put February and March below January in the example there and you have a filter for 3 items ( keep an eye to the range named there and increase as necessary, as you will need more than 66 rows…)

https://help.libreoffice.org/latest/en-US/text/scalc/guide/specialfilter.html

#3

@Wanderer Maybe I’m misunderstanding, but the Advanced filter function in that help page simply hides rows based on the table of criteria. I need to copy the selected rows from the master list to another location so that I can do calculations on the data and those calculations then feed to another sheet for more calculations. So I need every stock (ID) from the master list to have its own list at all times. When new data is added to the master list, I want the new information to be copied to the filtered list for each individual stock.

Does that make sense or am I just completely missing something?

#4

Just like the vast majority of today’s spreadsheet users, you try to use a spreadsheet as a database surrogate. To some extent this is possible. The single most “database-ish” feature is the pivot table.
https://ask.libreoffice.org/uploads/short-url/omMlzc26euKXULnKggksJ7XNh4U.ods

1 Like
#5

Yes, you missed the possibility to set the destination for the filtered cells.
.
In my setup I retrieve a table of data first from my database as a copy. (As this is only for early stage planning, I didn’t integrate it in the database itself.) This first sheet you can compare to your updated list of stock-data. This sheet is the source-area for the filter.
.
On the second sheet I’have the filter, just a list of ids like your possible stock-ids

54321
65422
31885

.
The third sheet is the destination of the filter. When I completed/changed my list of ids, I select a cell on the first sheet inside the source area and select from Menu Data → Refresh area (or similiar, my usual language is not set to english) and all related lines are copied to the 3rd sheet, so I can “play” with this dataset.

#6

Yes, you’re right, this is probably at the point where a database is appropriate. However, I don’t know how to use any database software so this is where I’m at. Is there an opensource one that I should look into and start playing around with?

#7

Base (the module of LibreOffice wich connects to databases) already delivered embedded HSQLDB and Firebird, wich can be used to start. For serious work most will recommend to move from embedded to a split-database, where your data is not in the .odb-file. Check the guide for Base on the TDF-website (At menu Get-help > Documentation)
https://documentation.libreoffice.org/en/english-documentation/
.
I’m using (besides dBase) SQLite and MariaDB (MySQL), but Postgres is also an option…

#8

Okay, so I took @Villeroy’s advice and created a database using Base for my dataset. I read through @Wanderer’s link for the Base guide and did the following:

Create a table and move the contents of “Transactions” sheet into the table.
Create a query for every stock.
In calc, turn on View->Data Sources.
Register the new database with right click menu.
Drag the queries into the worksheet and it imports all the data.

Calc automatically adds a Database Range in Data->Define Range. In this dialog select the created Import## range. Expand Options and check the boxes for Insert or Delete Cells, Keep Formatting, and Don’t Save Imported Data. The Insert or Delete Cells option is necessary to get new transactions added when the database is updated. The formatting one is obvious and the last is so that every time the Calc document is opened it asks to refresh the queries.

I was able to do this for new transactions added to my prior dataset and it seems to work just fine and the Calc file works much quicker and more responsively than before.

2 Likes

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.