Hacker News new | past | comments | ask | show | jobs | submit login
Show HN: Stock Trading from Google Spreadsheet
281 points by umitanuki on Dec 5, 2018 | hide | past | favorite | 84 comments
I've shared copy-able G sheet with full script that calls stock trading API. https://docs.google.com/spreadsheets/d/1RLvSmDEfS7U2OrqlUYvv...

I'm sure many of you guys do some trick over stocks in spreadsheet. Full explanation: https://medium.com/automation-generation/manage-your-stocks-...




I worked for one of the largest financials services companies in the world and they STILL use excel to drive their trading activities. They are so large and so complex that it's impossible to convert them because no one completely understands how it all works. They were developed by traders/market experts with no help from IT, for years IT didn't even knew they existed.

These spreadsheets are truly amazing, I've only seen glimpses of these spreadsheets but they were massive, nothing like needed to have servers in the local network cabinet and running cables for keyboard, video and mouse, because you needed 1/2 a Tb of ram in order to open the spreadsheet.


Also worked for some large financial firms, and can confirm that there is a disconnect between Excel models created by traders and IT. One interesting development was the acquisition of ClearFactr by Goldman [1]. Seems like they hope it will centralize some of these models into one sytem, making them accessible across the firm (Excel sheets can even be imported).

GS has had a centralized risk/pricing system for over 20 years now that is pretty powerful [2]. Fun fact: GS was able to calculate their total exposure to the Lehman collapse 12 hours after it happened using this.

1. https://www.businessinsider.com/goldman-sachs-buy-financial-... 2. https://news.efinancialcareers.com/uk-en/276170/secdb-quartz...


I worked at a financial services company that created portfolio analytics software. My job pretty much consisted of turning our analysts' excel models into high performance Scala code.

The company wasn't very successful, we just couldn't convince portfolio managers to pay for our software. Even the analysts at hedge funds didn't like to use our software. My theory is that a bunch of analysts need to justify their jobs with incredibly inefficient and error-prone excel workflows. Our software would have eliminated a lot of what they do in a day, and therefore, would eliminate their jobs as well.

It is terrifying that trillions of dollars of assets are run on excel, but the inertia is too strong.


> My theory is that a bunch of analysts need to justify their jobs with incredibly inefficient and error-prone excel workflows.

Even if you're right, pretty much any company whose customers aren't 100%, unimpeachably good is going to fail.

Another way of thinking of it is, as soon as they became even slightly an antagonist (think Disney rivals, less Autobots), it was doomed.

As an aside, an alternative interpretation would be that Excel workflows are more intellectually stimulating, so as a side effect they come up with better models. So even if your software had a superior UX for the task at hand, the objective of making money is better served by people using their brains harder generally.

After all, a lot of people practice violin and play chess because it feels good, and then those people make a bajillion dollars on the stock market. Consider that if Excel even remotely reenacts the patterns of those activities, which is almost certainly does, it makes money in ways besides calculating numbers in a user-accessible way.


I think your last paragraph is really spot on. Being an expert at excel makes you feel like a wizard, just like how emacs or vi or the bloomberg terminal makes you feel.

Our software didn’t have that feeling, it didn’t make you feel powerful in the same way that excel does (I lobbied hard for us to make a desktop app, no one likes how web apps feel. But nobody listened)


Excel also gives people the comfort that they can track what goes in and out and adjust easily.

Giving up that control is hard.


I don't think it's the fear of losing jobs, it's more because of the flexibility it offers. There's a lot of adhoc adjustments / corrections to data / formulas that require a lot of flexibility and a coded program won't be able to manage it that well. I used to do a lot of my stuff with vba but in the end I would still use excel formulas when I could


No, it would be terrifying that trillions of dollars are run by programmers. If the hedge fund guy says that Excel is fine, then Excel is fine.


Well considering their terrible returns net of fees and the massive outflows into passive, I think it's about time the industry did some soul-searching.


Principal number one; don’t overcomplicate yourself. Keep it simple, stupid


I have a humorous thought. Maybe it's that excel models require humans to run. The code and data is way messier and it's partially locked up in the heads of the people running the sheets. It's harder to copy, so it's harder to steal.

One cyber-breach and all that really nice clean code is readily understandable to your competitors.


Sounds about right to me! One of the companies I consulted for had a billing department that insisted people pay their utility bills by check. They said "this is the way most of our customers like to pay".

But what they really wanted to say is "we will have to repurpose 200 staff who process these checks and mail out statements, and they're all 30 year veterans, and I don't have the heart to tell them they're fired even though they are the sole reason we lose money"


The SecDB article [2], is very interesting!


People outside of finance really underestimate how important excel is in the finance world. It really is the lingua franca ( or the universal software ) of the industry. It is so entrenched that even google hasn't been able to move the dial. One of the major selling points of google spreadsheet is that it is free. What google didn't realize is that finance companies are wealthy enough to afford to buy or license software. As poor high school or college students, we all used google spreadsheet, but in the industry, we all use excel.

There are so much sunk costs ( decades of excel worksheets and decades of technical know-how ) that most companies aren't going to switch to another spreadsheet and migrate all that data and retrain their employees.


As a long-time finance developer, I can attest to this. I think the only company that will be able to kill Excel's dominance in finance is Microsoft, and sometimes I think that they're trying. It seems the past few releases (basically 2013 on) have alienated a lot of power users by needlessly shuffling the menus/toolbars, adding lots of graphics that slow down sheets and add nothing for people who already know how to use the app, and not keeping a laser eye on memory consumption/performance issues.


In our company we also have a lot of excel files. Most things should done different, but then we would have to work WITH the IT guys, but they have even to solve a simple ticket more than a week, some tickets are open for more than a half year. That's the problem why we do so much as possible without the IT guys. I know that's bad in so many ways ..


Excel just added similar functionality built in: https://support.office.com/en-us/article/get-a-stock-quote-e...


I can see that. Traders in Wall Street are too practical to build something maintainable. Now G Sheet brings the same power but to software developers this time.


> Traders in Wall Street are too practical to build something maintainable. Now G Sheet brings the same power but to software developers this time.

When did software devs lack the power to write trading tools? The issue is that the traders had the knowledge and wrote their ideas into a spreadsheet which was not only maintainable for them but easily extensible. I cannot overstate that second part. They become spreadsheet behemoths because it's trivial to make them do that one extra thing (at least for the first fifty or so features).


[flagged]


> When will y'all grow up and grow balls to make spreadsheets FASTER than it is today

Probably when the IT peeps in finance believe that the problem with spreadsheets is their speed. As the fundamental problem of spreadsheets in finance has absolutely nothing to do with their speed, this may take a while.


Trading driven from a spreadsheet brings back horrible memories of my earliest days on Wall Street. Excel was usually hacked in the most grotesque ways: tick data coming in, updating cells and signals and driving actual orders out the other end. Add to the fact that you're running on a desktop computer thats already struggling with a dozen other applications consuming market data, CPU cycles and driving 6 monitors...

I was usually called in to debug these things. My approach was always super minimalist... How do I make the 1 change that will get this all back on its feet again? .... without looking at any of the VBA or the weird DLLs lurking in the shadows.

Having said all that - Google Sheets is actually a different beast. I still wouldn't trade through it. Also, I've been eyeing Alpaca for a while - I like what they're doing. Best of luck to them....


When I was a trader at Merrill Lynch (NYC, 2005) I traded these basket securities on a Swiss exchange through a Rube-Goldberg-like trading system employing two Excel spreadsheets. Here's how it worked:

A spreadsheet on my computer would pull data from Bloomberg using their Excel API and would price the securities. Those prices would be pulled off the spreadsheet by some Reuters application that would then send those numbers through a 56K modem to Reuter's system. Somebody I never met in London would screen scrape the prices from Reuters into Excel. From that Excel spreadsheet the prices would be posted on the Swiss exchange. I was responsible for any resulting trades and they would go into my book.

These were two sided prices (bid-ask) so if the process broke and the market moved I would get picked off. Thankfully they were mostly all delta 1 instruments with 50 bps of spread so that didn't happen too often.


Edit: before I read the article I wasn't sure if you meant:

https://www.alpaca.ai

or

https://alpaca.markets


https://alpaca.markets/


Alpaca.markets is great! What are other alternative for personal solution?


Have you looked at Interactive Brokers? They seem to support more complex order types than Alpaca, which can meaningfully impact returns of a automated trading strategy.


IB is good if you don’t need them. I’ve asked questions via their website and called them to no avail. That being said they seem solid.


Quantopian ? https://www.quantopian.com/


And people trusted real money to such a crap solution?


In a kind-of defense of such methods: such technologies do seem crappy when you look at them today, but building good products with bad tools is pretty much in the job description for engineers and even more for experimental scientists. Check a good experimental scientist record of 100-300 years ago and you will be amazed what they did with the tools of the time.

This is not an apples to apples comparison to the spider fight world of trading, but there would still be a lot of ingenuity there. And a simple way to limit risk is to ruthlessly review the results, both automatically and manually. Keep going while it works as expected and stop/adjust if there is a major glitch.


Like the old joke, you just have to outrun the bear. It just had to be better and get to market quicker than the competition. It could probably beat the guys doing it with desktop calculators quite handily. Also, this is the financial services industry: nobody cares how much coke you're snorting if your profits are good.


In the valley of the blind, the one eyed man is king.

A crappy solution was still the best they had at the time. You're going to hate to read what they say about today's solutions in 2028.


You'd be surprised at how much this is still the case.


I remember a while ago there was an article posted here on HN that explained most ATMs are just FTPing txt files back and forth. The amount of fragile, weird systems that underpin our financial world seems deeper every day...


As opposed to another crap solution some small band of programmers could come up with?

If it works...


There was a really nice example of a Vue app that uses Sheets as an API on Codepen the other day - https://codepen.io/quinlo/pen/eQrRMM


I don't think many people realize the flexibility of Google Sheets + Scripts.

When I need an ad-hoc flexible internal tool, Google sheets is usually the first place I start building.


I'm actually building a product right now that will live entirely on Google Sheets.


Awesome, can you tell us more?

I'm building out "Idea Meritocracy" tools like those found in Principles(Dalio).

The Google Forms integration is really powerful for mobile input. And event triggers are great for managing what's inputted to the form.


Should be ready for beta release very shortly. Focus is on improving communications between small businesses and their customers. I thought about building out a standalone web app, but Google Sheets was easy enough for a MVP. Spreadsheets are generally well known and familiar enough for small business owners/operators to use so it could always live as a sheet.

There are a few competitors right now, but they lack a certain feature that I think is important.

Can share more when ready... aka when I've purchased the domain name so it doesn't get squatted.


So it's a CRM? Or a single CRM feature?

Does it use any proprietary or other APIs outside of the sheets scripts?

Ping me when it's ready for beta testers

Brian at angularjobs.com


No not a CRM. It's likely a feature in many CRMs though.

It uses an outside API and that's a big change from existing solutions. Sorry for being cryptic, but if I shared the API it would be obvious. I just want to put final touches on it before doing a Show HN. At that point it'll be obvious :)


Keen to see this. Can you please reply to this message when you share to HN?


Reading that book now and would be interested in hearing more about what you're doing. Coding your principles in a decision tree?


Skip to the Appendix and you'll see exactly what I'm talking about.

Also, I'm managing the "5 Step Process to Get What You Want Out of Life" in the same spreadsheet.


and the apps script that can run inside Google Sheets is pretty neat as well


Does something like this exist in Europe? Free trading would be a dream. Paying 7,5 EUR to 15 EUR or more for each transaction gets old quick.

Free: Or paying a very little amount compared with what is considered the norm.


Not free, but Europeans can sign up with interactive brokers UK and the pricing is very good for US markets.

>Paying 7,5 EUR to 15 EUR or more for each transaction gets old quick.

Even if there was a free service in Europe I'm not sure I'd use it due to concerns about fill & execution quality. Or to use an IT analogy...if you're not paying you are the product. In this case via order flow being sold.


IB is the easiest American one to get for Europeans. It's been around for a while and plenty of respectable finance people have been using it (for personal trading).

If you travel to the US most major ones should open an account for you too. But they ban foreign IPs sometimes and unlocking can be hassle.

The only major problem with the US I'm aware of is the death tax exemption for non-residents is only 60000$, so you don't wanna die or need to structure appropriately.


https://stocks.getbux.com/ we will be launching it next year!

Disclaimer: I work at BUX.


Any plans for trading API? I presume you are from Netherlands, what exchanges you'll cover? What are limits to EU vs non-EU residents?

EDIT: formatting...


Thanks, will have a look!


Try KloudTrader

https://KloudTrader.com/Narwhal

Our brokerage is through Tradier.


These guys have actually been advertising on the radio lately (in the Los Angeles area).

  https://www.robinhood.com/
They are claiming zero commissions on US based securities, but then, how are they making money ?


https://news.ycombinator.com/item?id=18414651

https://news.ycombinator.com/item?id=18223972


Try degiro?


yep, https://www.degiro.eu/ has ~0,5$ fee on NYSE and NASDAQ


Thanks, will have a look!


You can just use Google Finance directly from your Google Spreadsheet.

Reference: https://support.google.com/docs/answer/3093281?hl=en


I can't believe I've never heard of alpaca before.


It looks super interesting, but it's unclear to me if you can buy/sell options.


Alapaca's business model [0]:

> Alpaca will start offering Premium Plan subscriptions, where premium users will have access to various perks, such as higher quality live data feed and more computing resources. The pricing and exact features have not yet been determined. Please stay tuned for updates.

[0] https://support.alpaca.markets/hc/en-us/articles/36001104717...


Looks like the press release was only a few weeks ago. I wish they provided some way to access historical data, however. I struggle to find accessible and inexpensive histories.


You will access to the historical data. https://docs.alpaca.markets/web-api/market-data/ working to cover more


Smart to have a SUBMIT button.

POST requests with Google Sheets is generally a bad idea since you can't explicitly control when they're sent.


So many firms still use Excel to drive trades. When I started out I was using GNumeric and still sometimes use it for quick ideas before coding a strategy in c++


These common excel spreadsheets used by investors are partly why I am writing my web application. It focuses on DGI investing and dividends in general and I have a lot o plans and room for improvements, but e.g. automatic dividend calendar must be 100 times better than keeping track of dividends in google spreadsheet manually :D


DGI == Dividend Growth Investing


I'm really curious what a simple algorithm would be? I know that penny stock traders usually operate within the first hour of trading mostly looking for stocks that are poised to gap open on some event looking to catch upward momentum, even if only 1% or 2%.

Would be awesome to see how this could be accomplished with the Alpaca API.


removing emotion from the actual decision to make equity purchases should be the main priority for products right now. Would be great to see more programs "building your own ETF".

- choose your investment theory - set the variables - get monthly/quarterly updates to SELL:X / BUY:Y


Nifty! What’s the catch? ;-)


Cool, but first thought was a sarcastic “What could possibly wrong?”


Is there a catch to Alpaca? How do they make money?


Companies like that usually sell the order flow to market makers.


Good explanation of Robinhood's business model by Matt Levine: https://www.bloomberg.com/opinion/articles/2018-10-16/carl-i...


And by Evan https://medium.com/automation-generation/commission-free-tra...


I know Robinhood makes hundreds of millions with this, do you know how large the full market actually is?


Which is a nice way of saying you're in a pool of bait for high frequency traders.

...but that's really not so bad. Having an algorithm skim 2 cents/share off of you is a lot better than paying $5+ per trade (assuming you're not trading 1000s of shares, and you're not in penny stocks or options where spreads are big).


It's not a nice way of saying that, the "pool of bait" logic is totally wrong. There's a lot of nonsense written about high frequency trading on the internet, but for example Matt Levine's explanations are completely readable, correct, and on point (see the other comment).


If someone will pay to trade with you instead of someone else, you're definitely not getting the best price.


There is no free lunch... To paraphrase a friend from high school: "...when you start to play and don't know who the patsy is, usually it's you..." ;-)


I wish Alpaca worked for Canadians. There is Questtrade, but comparing the documentation, it isn't as nice to work with.


We are working hard to support non-US users!


Cool. I like useful posts. All posts should be useful. But not all posts are useful. Most are not useful. Now I have lost my faith in humanity. :(




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: