path: root/transcripts/lightning-hack-day/2018-10-27-pierre-rochard-excel-in-lightning.mdwn

Pierre Rochard

Excel in Lightning - Lightning Hack Day NYC

Video: https://www.youtube.com/watch?v=CWIskx9KmAg&t=0s&list=PLYLOctWdUBtYBle_aU4WWJJaTekm8bhct&index=5

# Demo

I want to present a project I’ve been hacking on for the past month which is getting lnd inside of Excel. I’ll explain why someone would ever do that. I want to bring Lightning to the most popular business platform in existence today which is Microsoft Office Excel. It’s installed on hundreds of millions of computers around the world. It is used day-to-day by all sorts of business people who are doing adhoc data analysis. It is also abused by business people who are creating databases and creating full fledged applications within Excel which is probably not a great idea. You should probably be using a SQL database but it’s a platform that is so friendly to non-programmers, to non-developers that they end up becoming developers on the platform. I thought it was very important that we find a way to have Lightning inside of Excel. Business power users who want to experiment with lightning, they don’t have a client. It is either something that is very minimalist and tries to abstract away as much as possible. That’s for consumers, like iOS and Android wallets where you can just click once and then you pay. Or you’re a developer and you’re in the command line, you’re using ln-cli. You’re getting your data that way. If we want something in between for power users, that’s a missing tool. So what I did is I have lnd bundled into an Excel plugin. This Excel plugin allows you to take a normal Excel workbook that you create and then you do Set up Workbook and it automatically populates these tabs with data from your lnd node. The lnd node can either be embedded inside the plugin or it can be one that is already running locally because you might have been experimenting with a different wallet or a different UI. Or it can be a remote node because for example you might have a BTCPay Server already set up and you want to be able to connect to that. You could also share a macaroon with your accountant that is read only. They can get all the data into Excel like that. From a technology perspective, I’m using the gRPC API with C# bindings that I generated. It works really well. Granted, there’s areas that the gRPC API could improve but that’s probably a different presentation. Let me do a quick show around. Basically this is the configuration. If you want to be on mainnet you can type mainnet and then you can hit connect again and it’ll connect to your mainnet node. You could be connecting to a remote host. You currently do have to provide the macaroon and the TLS certificate but hopefully we’ll find ways to make that less painful than it currently is. Then the password. You can see it is all very secure. It uses advanced font and background in same color encryption to improve your opsec. This is just getinfo from the node. You can see your peers. Some of the next steps I want to have is to add a peer from this view. Currently you still have to do that in command line. I want to have it where the entire lnd API, anything you could do with it you can do from within Excel. I’m just not there yet. This gives you your wallet, balances. Your onchain and your offchain. I’m going to skip the transactions tab because currently there’s a bug in lnd and it will cause lnd to crash if I do get transactions. So here you see we have an open channel. I just opened it. It is disappointing that it is inactive because that means we’re not going to be able to route any payments today. Pending channels - I should probably close that inactive channel and open another one. If I close it, it’ll show up in one of these tables. Then closed channels and payments. Unfortunately I had a database corruption issue this morning and so I had to wipe all my directory and start from scratch. So I don’t have 80 payments in here that I did yesterday. This view allows you to send payments. So you would go get this test one and then you’d paste in the payment request. It decodes it for you. It also queries routes so I don’t get any results for query route right now because I don’t have open channels that are active. If I did, this would offer up different routes and the different fee that I would pay for it. Then you would hit send payment, this is going to fail, unable to find a path to destination. If it did find one then it would give you the preimage here and the summary of the route that it did end up taking. That’s lnd + Excel. Any questions?

# Q&A

Q - Are you running it in Excel? 

A - Excel has several different ways to program it. The immediate user facing way to program it is to… a bunch of functions inside of Excel. I’m not doing that. The second hardest way is to do it is to do a VBA macro. Then you can do a plugin in dot net languages like C#. That’s what I’m doing in C#. Anything that you would have access to in C#, you would have access to in this environment, including gRPC, including if I wanted to I could do a REST API call to bitcoinaverage.com. Then I could have the current dollar value of the payment I’m sending. It’s definitely something I want to include eventually. To your question, how does it actually take this data and then display it. Excel has an interop API that allows you to change every single thing about an Excel workbook that you can change manually through the UI. So you can change all of these borders…its definitely going to create them programmatically so I don’t have a line of code for each border. But it’s a function in there of like create borders.

Q - Do you see this being useful for routing nodes?

A - Plausibly. Right now, autopilot is very much a work in progress / only beginning. If someone is a data scientist and they’re trying to figure out how to improve autopilot, plausibly they would do that in Excel but really they should be doing that in Python or R. That’s something I’ve been thinking about as well. Having it go into pandas in Python so that data scientists who should be thinking about how to optimize routing nodes would be doing that. If someone is a routing node capitalist, they’re not designing the autopilot algorithm, they’re just an end user of the algorithm, they want to see the profitability of their routing node. Then yeah this would be a great interface for them to fly in and see what’s the total routing fees, what’s my lightning network rate of return. This was the initial impetus for me creating this. Nick…, I was talking about the lightning network rate of return with him. I was like “So have you calculated some things?”. He was like “I don’t really have a way of doing that, I’m not a developer”. I was like “Yeah you would really benefit from having all of this in Excel”.

Q - I know this is baby steps and everything. Do you see a QuickBooks implementation?

A - For sure. I think that there’s a fundamental difference between payments data and accounting data. They are sort of related to. But a double entry bookkeeping system is fundamentally different to a cash system. So really what you would want to have is your lnd data as a subledger which gets rolled up to a general ledger that’s running in QuickBooks, in NetSuite, in Oracle and SAP and transforming the subledger data from your cash ledger into your accounting. That could be done in Excel. I think it would be better if you have a developer create an integration that directly connects those two. Really I see this more from an operational perspective of someone who is interested in payments per se, not necessarily part of the wider accounting issue. Within an accounting department   we have a treasury group that handles the movement of money. So this would be used by a treasury group and then your accountants would have a read only macaroon that feeds into the actual accounting database for recording double entry transactions.

Q - Just lnd or other things in the future?

A - Yeah other things in future for sure. I felt like that when I started out, when I saw gRPC, C#, it just made sense to go to lnd. I think that c-lightning’s architecture is fascinating. It seems to be targeted towards a server environment. I wanted something where the person can have their lnd node bundled into the plugin so that they can be on the network just by installing the plugin and hitting one button to set up the workflow. A lot of love for c-lightning but it didn’t really fit into this particular use case. Maybe it will make sense to have both options.

Q - Was there anything about lnd or lightning…

A - Definitely more of the latter. It was amazing to me when it worked at all because this is an experiment on top of an experiment on top of an experiment. So what I found to be easy to work with…. I was actually surprised that autopilot was able to get channels open that were routing to Y’alls right out of box. I was surprised that Neutrino worked out of the box as well and this is on testnet. What I found frustrating from a developer experience point of view was that there are limitations within Excel for asynchronous behavior. On top of that, I’m not entirely familiar with the asynchronous capabilities of C#. On top of that, am I not using async correctly or is lnd screwing something up? It is kind of like a black box in that regard. Some of the error messages are a little opaque. I was debugging my macaroons for a few days because the macaroon moved from 0.4 to 0.5. The error message didn’t really help me with that at all. I felt that I was not decoding or not deserializing the macaroon in C# so that was frustrating but that’s the cost of doing something that’s cutting edge like this. Then there’s other weird inconsistencies within the lnd API in the gRPC. Like pub keys, is it pub_key or is it pubkey one word? That’s not consistent at all. If you do gettransactions, it is get transactions but everything else is listchannels, listpeers. So it is like we need to have a consistent API. I get that’s it’s alive, it’s evolving and hopefully we get that consistency converging over time. We’re allowed to make breaking changes whilst it’s still in beta.

Q - Could you use the optimization function to figure out if something…?

A - That’s interesting. Solver and the more advanced features definitely could be. I feel that would be part of an adhoc analysis as someone who’s trying to figure out a better autopilot algorithm. Could prototype it in Excel and then code it in Go inside of lnd. I think it would be a really good playground for prototyping things like that. In terms of turning it into production code then you would want to go into Go. The other thing with Excel is you get all these pretty charts and graphs and stuff so I want to take some of these tables and create visualizations that are dynamic. I’ve been laying the groundwork for that.