🏷️ Add Last Closing Price Column to Database

[JamesAlfonse]

Enhance the WhyDRS Database by adding a new column that stores the last closing price of each stock. This column should automatically update once a day to ensure that the data remains current and reliable for investors.

Tasks:

• Modify the database schema to include a new column for the last closing price.
• Set up a scheduled job (e.g., a cron job) to fetch the latest closing prices daily from a reliable financial data source.
• Implement error handling to manage data fetching issues or discrepancies.
• Update the data entry and update procedures to accommodate the new column.
• Ensure that the front-end displays the last closing price appropriately.
• Write unit and integration tests to verify the functionality of the new feature.
• Document the changes in the database schema and update any relevant documentation.

Expected Outcome:

• The database includes a new column that accurately reflects the last closing price for each stock.
• The last closing price is updated automatically every day without manual intervention.
• Users can view the most recent closing prices, enhancing the usefulness of the database for investment decisions.

[JamesAlfonse]

I'm thinking this should be two columns. One column for Last Closing Price, one column for Closing Price Updated On [date]. Would love some assistance from the community on finding a free and restrictions-free dataset from a reputable source. Ideally two of the columns in the Google sheet can be repurposed for this (from the first 28 columns would be great) but I can work around it if needed. @JFWooten4 @BibicJr @tehchives @apes-on-parade
I can't tag people that aren't part of the organization, maybe we can add bob and greengiantyo as members? We should also think about establishing a process of how we go about adding members.

[tehchives]

Great catch on the members tagging. I would love to be able to directly tag anyone who has created an issue before, for example, or automatically welcome as member with limited perms a person who had done so. I wonder if there's a way to expand that feature?

Per the data - It seems like a variety of different popular websites do have the ability to download bulk CSV data on a per ticker basis. Examples:

Marketwatch: https://www.marketwatch.com/investing/index/spx/download-data - Select any ticker, then select the Historical Quotes tab, then hit Download Data to get the last year of information. It also automatically displays key information per trading day with the most recent appearing first.

Going per ticker and querying for a download every time it is updated would be pretty intensive I imagine. Maybe it would be easier (and I don't know if this is true) to scrape the plaintext on a website (or group of common websites, like marketwatch, yahoo, tradingview, etc) and have our prices display in post an average or plurality of what those sites display? This avoids dealing with direct downloads, storing data, or needing to ever request an API.

[JamesAlfonse]

Thanks @tehchives, a script could be made to pull the data from MarketWatch though I'm not sure their ToS would allow for this for thousands of tickers daily. Correct me if I'm wrong, would we need a service or site that allows commercial use of their data? From what I'm reading, Alpha Vantage might be the closest fit for the price of free, though that may mean that closing price gets updated roughly once a month.

[tehchives]

It's a great question and honestly I am not sure of the correct answer. Do these entities like Marketwatch pay NYSE and NASDAQ for access to their API for pricing updates? It's something we will need to research or consult an expert on. Now that WhyDRS 501c3 status is approved, we might be able to find grants or special accesses to data as well.

[JFWooten4]

Thank you @JamesAlfonse for the thoughtful approach to member inclusion here and in WhyDRS/.github#15! Very important (for me personally at least) to let community members display their passionate involvement with the group through a digital "Organization" profile badge. 💜

Thank you @tehchives for coming up with some preliminary ideas to achieve James' innovative price idea. Never would've thought of (a) associating the companies with their stock price or (b) going through the public csv download route for open data.

Both great ideas diving at a relatively deeper systemic flaw of the current market infrastructure, which Chives just got at. Yes, the entities we'll be familiar with on the data interaction layer do pay the SIPs. NMS actually lays out the revenue split and operating hierarchies of the three subprocessors and their interaction with the exchanges of the Americas. A couple years ago, there were actually some House Fin. Services Committee hearings on lowering the statutory access fees, which haven't changed since the Recession. Needless to say, we didn't have a seat at the table back then, and the support for change from Dave wasn't enough to push that reform over the finish line.

Price Data

Until we get the trading on-chain, we should be able to access everything we need with a public API service for under $100/mo. It's really only the brokers that get the live L2s from the SIPs for exorbitant rates. For us retail users, a delayed feed from a middleman like Polygon¹ is the norm. They should and do aggregate all the (latency-delayed) info from the million different venues. Given we only want closing prices right now, a very basic public implementation should work just fine.²

Never looked into discounted 501(c)(3) feed rates - good idea! Wall St. helping out charity for once:³

Tagging Users

As an aside, the @ shortcut might not autocomplete with users outside of the organization. But you can still just write someone's username thereafter to mention @Someone.

Footnotes

1. Relatively high free tier. ↩

2. I've seen peers with more active needs contract directly with Tradingview and, in special circumstances, with an exchange or broker (if you only want to route via that middleman). But these cases really only affect the active trading interface, and they generally cost thousands of dollars a month. ↩

3. At the very least, some of the more fintech-centric middlemen ought be a smidge more responsive to a call to "donate" proprietary, gatekept data if we ever need more than a simple daily free public snapshot. ↩

[JamesAlfonse]

@JFWooten4 Thank you for the suggestion, it sounds like Polygon may be the best option for now. Here is a further look into how Polygon gets their data (screenshot below) @tehchives

Also extremely helpful to know that I can tag people outside of the organization. Just when I thought I got the basics of Github down there's something new to learn.

[JamesAlfonse]

https://chatgpt.com/share/67de5e11-779c-800c-9ffa-38a3d10ac7d4

I used ChatGPT's Deep Research tool to see if the Polygon API could assist us here. It essentially reiterated the same points that were brought up by @LastResortFriend about restrictions for redistributing the data coming from data providers. Regardless, I asked it to come up with an email template to reach out to the Polygon team asking for clarification and assistance. Please take a look and let me know if there's any adjustments than can be make. @JFWooten4 @tehchives

[tehchives]

Sorry for late reply on this James! That template is fantastic. I know we talked a bit about this since in other spaces but wanted to make sure to leave that note here as well. Did you end up sending out this email already? If not - I'd definitely suggest to use your whydrs.org email to do so. May come across that much more official :)