Broker guide/broker data consolidation

[JamesAlfonse]

Currently there are two google sheets that have broker data/broker guides. Ideally it would be consolidated into one that gets continuously updated. This one has more up-to-date information, and this one I believe has not been updated in a while. Once consolidated, it would be easier to implement a way to search for brokers by country as suggested by @tehchives here.

As far as implementing something like that, would a table with filters and a search bar similar to the one that exists on (database.whydrs.org) work here? Or do we go a different route? @BibicJr

[JFWooten4]

🧠 Love how you emphasize the impact/benefit of this upgrade @JamesAlfonse 👏🏼 Yes, I agree that projects are the most common way for cross-repo organization. 🙌

[BibicJr]

I've been updating both spreadsheets concurrently, so they should have the same information. Although the first one you linked (drsgme translation sheet) has the additional info of what drsgme guides were translated or not.

If there's a way to do this I think this would be the best solution: I think the whydrs database spreadsheet should be the primary resource we update and the drsgme translation spreadsheet should link to the info on the whydrs database for all broker info. Then we can update just one, but are also able to keep track of the drsgme translations. I'm not sure how this would work if we were to add additional brokers/rows to one spreadsheet though.

[JamesAlfonse]

Oh! I didn't realize you were updating both concurrently. Fantastic! I can try linking the two sheets so that your sheet gets updated with new info from the whydrs database sheet. I can also just pull certain columns and rows from one sheet so that you don't have to manage two worksheets. I pulled a current version of the sheet and working on cleaning that up before getting an automation up and running. If there's additional brokers to add, just let me know and I can adjust the scripts. See here

Should the whole table load, or do you want pagination with 10 entries per page?

[JamesAlfonse]

@BibicJr If it works for you, I can copy your sheet into the main worksheet so that only one automation gets created

[BibicJr]

Pulling certain columns from the whydrs database would be ideal. Rows would complicate the translation links so we can ignore that, and I can always manually add extra rows.

I only ever look at the sheets themselves, so I'm not fussed about the pagination or not, but I'm guessing the pagination makes it lighter to load?

"I can copy your sheet into the main worksheet so that only one automation gets created" Does this mean the drsgme tanslation sheet would become a new page/sheet in the whydrs database? That might make more sense logistically, I just wasn't sure if we'd be allowed to have these resources be in the same space (as drsgme is for a specific ticker it would be frowned upon by non-profit regulators/auditors (I don't know what you'd call them))

[JamesAlfonse]

Pulling certain columns from the whydrs database would be ideal. Rows would complicate the translation links so we can ignore that, and I can always manually add extra rows.

I only ever look at the sheets themselves, so I'm not fussed about the pagination or not, but I'm guessing the pagination makes it lighter to load?

"I can copy your sheet into the main worksheet so that only one automation gets created" Does this mean the drsgme tanslation sheet would become a new page/sheet in the whydrs database? That might make more sense logistically, I just wasn't sure if we'd be allowed to have these resources be in the same space (as drsgme is for a specific ticker it would be frowned upon by non-profit regulators/auditors (I don't know what you'd call them))

I see what you're saying. Yes there should be segmentation between the translations and broker data. Having two sheets would be a quick and easy way to do it, a simple formula can automatically populate rows and columns from the Broker-Data sheet into a separate Translations sheet within the same file. As long as the Google sheet is not front-facing, do you think that that would work?

I looked at the share settings for the Google sheet and it looks like these sheets are available for public viewing:

[BibicJr]

If it can be done to different sheets in different files that would be the best, but if not then it's not the end of the world.

And yeah, all of them are viewable by the public. We definitely don't want to hide this information, just limit the edit access.

[JFWooten4]

I'd be interested to see if we could preview/change all data directly in the central git repo locale, so that we aren't worrying about synchronizing between desperate sources. For instance, if we could get the guides into the same place as the database, then it could be single to cross-reference onto a shared page on the same site for the guide. Do you think that could make it easier to auto-fill the request forms as needed?

[JamesAlfonse]

I'd be interested to see if we could preview/change all data directly in the central git repo locale, so that we aren't worrying about synchronizing between desperate sources. For instance, if we could get the guides into the same place as the database, then it could be single to cross-reference onto a shared page on the same site for the guide. Do you think that could make it easier to auto-fill the request forms as needed?

Would something like this work?

[JFWooten4]

Would something like this work?

I tried compiling this in a local Space, but I didn't see any page content on the index here. Is it trying to import some kind of web2 DB embed?

I understand per this weekend's conversation that this originated from the prior DRS movement, so for me the question is exactly what kind of data we're trying to consolidate in these jsons?

[JamesAlfonse]

Would something like this work?

I tried compiling this in a local Space, but I didn't see any page content on the index here. Is it trying to import some kind of web2 DB embed?

I understand per this weekend's conversation that this originated from the prior DRS movement, so for me the question is exactly what kind of data we're trying to consolidate in these jsons?

The local instance you're running is probably having trouble opening javascript, which is the same issue that I've had. Same goes for the database repository html file. You would need to either run a local server with these files, or upload the files onto IPFS and load the page that way.

The data in this repository is just downloaded csv of this sheet that I cleaned up (replaced empty cells with a space) and turned into a json file. Some of these files/folder structures will need to be reworked, as it would make more sense to integrate the data with the other databases onto one page as discussed here.

Whether the data is going to be integrated into one page or multiple pages, both can be done using one repository. Should I proceed with deleting this one, or keep it for now?

[JFWooten4]

The data in this repository is just downloaded csv of this sheet that I cleaned up (replaced empty cells with a space) and turned into a json file.

First of all, this is awesome. An unbelievable amount of work behind all this, and excited that it can be reasonably exported into a common standard. Re limited centralization,[^orp] perhaps our first steps could revolve around migrating all this into the overarching Database repo?[^right]

Obviously this aligns with the larger question in https://github.com/WhyDRS/Database/issues/25, and I'll return to my question of what this Broker Data winds up used for by end visitors.[^info] It seemed like the broker integration was central to the popular frontend "search application" (below), similar to the issuer/TA info. Accordingly, might I ask if "data is going to be integrated into one page or multiple pages" means putting everything under a dynamic single Fleek deployment?

[^orp]: See past discussion on challenges with scaling using Gproducts around the DB. [^right]: This implicates the requirement of checking that all LICENSE rights comply. [^info]: It seems I might be looking at a different JSON than you in this limited context, as much is yet to be viewable without knowing all the link hubs.

[tehchives]

Obviously this aligns with the larger question in WhyDRS/Database#25, and I'll return to my question of what this Broker Data winds up used for by end visitors.3 It seemed like the broker integration was central to the popular frontend "search application" (below), similar to the issuer/TA info.

To respond to this bit here with what I think you might have been asking about, the main envisioned use case for this was to create a dynamic broker guide tool. You are likely familiar with the guides that Bibic built for DRSGME, and how those guides are sorted by broker. Throw had called it a DRS request. I think we talked about it a bit on the Saturday call but I'm not sure if you were in the call yet. The idea is to use the database and some basic user query for broker/ticker and spit back out the relevant information that a user might need in order to complete their DRS request (such as fees or context info about the TA involved).

Some brokers ask for more info than others. Fidelity is very simple and asks for zero outside info, but other brokers will ask for info about the TA, the stock itself, or other tax forms to be completed (especially for international investors).

As we develop more detailed resources for a larger subset of brokers the plan is to have a template that gets fed into by the spreadsheet, and spits out for the user a quick resource so they can take care of things. @BibicJr and @apes-on-parade met in the past to build a logic tree of how this might operate, I'm not sure exactly where that logic tree is but perhaps they could share it / we could bring it here.

[JamesAlfonse]

The data in this repository is just downloaded csv of this sheet that I cleaned up (replaced empty cells with a space) and turned into a json file.

First of all, this is awesome. An unbelievable amount of work behind all this, and excited that it can be reasonably exported into a common standard. Re limited centralization,1 perhaps our first steps could revolve around migrating all this into the overarching Database repo?2

Obviously this aligns with the larger question in WhyDRS/Database#25, and I'll return to my question of what this Broker Data winds up used for by end visitors.3 It seemed like the broker integration was central to the popular frontend "search application" (below), similar to the issuer/TA info. Accordingly, might I ask if "data is going to be integrated into one page or multiple pages" means putting everything under a dynamic single Fleek deployment?

Footnotes

1. See past discussion on challenges with scaling using Gproducts around the DB. ↩
2. This implicates the requirement of checking that all LICENSE rights comply. ↩
3. It seems I might be looking at a different JSON than you in this limited context, as much is yet to be viewable without knowing all the link hubs. ↩

Great points to bring up. I can start working on a branch with a more clear folder structure laying out the groundwork for how things can be potentially integrated in the other repository. Just taking a look over how the database workflow is already set up, one script pulls all the data from the google sheet and inserts it into a db file and then a json file gets created from the db file. We can use multiple db files for the three different tables, turn those tables into seperate json files, and then load those files into one dynamic fleek deployment where the end-user can switch between the tables. In a nutshell, I was thinking the full tables view would be more of an "advanced view" feature that end-user could toggle between that and the query search that @apes-on-parade built (see below).

The table/sheet that has Broker-Data includes columns that may be relevant to end-users, such as Language Spoken, Direct DRS Available, Domestic US, Transfer to another broker, Transfer to IBKR, Cannot transfer, currency fee, and customer service phone number just to name a few. This would be useful for people looking to compare broker options available to them, which may not be available at a glance with a simple search or using the DRS Request Template.

I wasn't thinking about integrating the DRS Request Template in the database repository, but that would be a discussion worth having on if/how that would be implemented.

[JamesAlfonse]

I wasn't thinking about integrating the DRS Request Template in the database repository, but that would be a discussion worth having on if/how that would be implemented.

It would probably make more sense for the DRS Request Template to live in the website repository, but I think it would be ideal for it to be able to reference data in the Database repository.

[JFWooten4]

I think it would be ideal for it to be able to reference data in the Database repository.

Not sure about you @JamesAlfonse, but I've been deeply working with corss-referenced pages and data for a few months now. It seems to function fastest when you can just move between the same source, rather than separate sites (incl. different subdomains). I agree with you ethos here (and championing approach to challenge in the parallel chat).

After circling this around a couple other community members still caught in 9–5 work, they seem to form a consensus around your thoughts and the approach from Chives. I'll defer to this view because I agree that, whatever all this nomenclaturally turns into, everything presently seems to revolve around a shared dataset. If it's all based on the same topics, then Jack suggested revisiting the data once a year to see if the organization still works for our needs.

I think we have a unique opportunity to build a very special decentralized site under the current Database. Maybe it turns into the more trafficked website, compared to the gatekept central Wix instance. Whatever happens diction-wise, the foundation certainly lives today for a valuable consolidation.[^next]

[^next]: If we agree and indeed migrate this data and such to the main repo, then perhaps we could transfer this issue over there (for maintaining public, transparent accountability) and delete this repo?

[JamesAlfonse]

Not sure about you @JamesAlfonse, but I've been deeply working with corss-referenced pages and data for a few months now. It seems to function fastest when you can just move between the same source, rather than separate sites (incl. different subdomains). I agree with you ethos here (and championing approach to challenge in the parallel chat).

Great! It's reassuring to hear that you've done it before. We figure out how to best do that when the time comes.

After circling this around a couple other community members still caught in 9–5 work, they seem to form a consensus around your thoughts and the approach from Chives. I'll defer to this view because I agree that, whatever all this nomenclaturally turns into, everything presently seems to revolve around a shared dataset. If it's all based on the same topics, then Jack suggested revisiting the data once a year to see if the organization still works for our needs.

Thanks for getting community feedback on this. Great idea to revisit this structure in a year, I set myself a reminder for this on my phone.

I think we have a unique opportunity to build a very special decentralized site under the current Database. Maybe it turns into the more trafficked website, compared to the gatekept central Wix instance. Whatever happens diction-wise, the foundation certainly lives today for a valuable consolidation.1

Footnotes

1. If we agree and indeed migrate this data and such to the main repo, then perhaps we could transfer this issue over there (for maintaining public, transparent accountability) and delete this repo? ↩

I'll initiate the issue transfer process now (did not know that was possible!) and delete this repository once that's complete.