Excel Crypto Portfolio

This is an Excel cryptocurrency portfolio I've been working on for myself. It grabs stats on coins from CoinMarketCap.com and keeps track of transactions and what wallets/exchanges you use. I've made it into a template to share with you Veeky Forums:

mega.nz/#F!OQFAnBIA!jTYWnDUg3vIlpAxaRMdzEg

I've separated the script module from the spreadsheet because people keep thinking it's a macro virus, even though it's not. In order for the spreadsheet to grab stats, you need to hit Alt+F11 and import the file named Cryptocurrency_Module.bas, save the spreadsheet, then restart it to have it load stats.

If you want to see what's in the module, just open it up in notepad. You'll see this: pastebin.com/raw/wFBkinHB which, as you can see, just grabs data by URL from the CoinMarketCap API.

Feel free to post any suggestions for improvement as well.

If you like my work and would like to donate:
BTC: 1H3byn8uySsrEqWmX2EvZBCM4KYvd6xJ7z
ETH: 0xab907b05Eaf8fBFdc2CeA6e2e2Fd57ddBE08F71f

Enjoy!

Other urls found in this thread:

github.com/VBA-tools/VBA-JSON
twitter.com/SFWRedditImages

Don't download just tried it out and was 23 minutes of child porn.

Good luck in jail friend.

wait seriously?

Yea was an old one with vikki

Did you guys even click the link?

It's an excel file and a module to connect to CoinMarketCap.

its a .bas file and .xlsm file, its not a video files.

Just letting you guys know that if you rename the file, you'll probably need to update the formula for your portfolio value in the middle of the chart shown in The old formula:
=ryza_crypto_template.xlsm!portfolio

The new formula:
=YOURFILENAMEHERE.xlsm!portfolio

Anyone try it out yet?

What do you think?

Bump

Could you stop being so paranoid Veeky Forums?

Just check it out.

nice try, fbi. I'm not going to jail for this.

Downloaded it on my old laptop OP, since you probably riddled it with viruses

But no, it's actually fine. And I was looking for something like this so thanks

I'd recommend re-building it in Google Sheets, so people don't have to be scared of malicious scripts

this, put the code on github so we can look & modify before we download or install

people have over 500k in crypto here man and for the one with 1k crypto, it's literally their life savings. I'm not going to risk it

Amazing. Really amazing. Is that how it feels to be rich? Being a scared lil' animal fearful for your money?

I probably could, but honestly I wanted to use Excel because, believe it or not, Google Docs can lag my lil' Lenovo Thinkpad. I love it, but it's not too powerful. So yeah, an offline portfolio was my best bet.

But I guess I can see how people might find Google Docs more convenient.

Thanks for the advice. Anything else actually related to the spreadsheet though?

how do I do this in libreoffice? Not using your botnet.

Learn how to use formulas?

I don't know if libreoffice uses the same formulas as Excel. Seems probable.

And it's not a botnet, fool.

it is a botnet. Excel is a botnet. Don't you browse /g/?

Not lately. Gotchya.

I guess I could try to redo this all in Google Docs. Though isn't that a botnet too?

i dont want a keylogger on my pc

y'all need to learn how to code

I looked at the pastbin and it's about 99% the same as I what I wrote for myself

it's just an http request to the coin market cap API which returns the coin data in json

That's alright. You're smart, aren't you? I think most of Veeky Forums probably thinks they are smart too, don't you guys?

So you probably already know how to open up programs and such in sandbox environments like virtual machines, where you can safely destroy the virtual environment without destroying your actual PC. Or even to just upload a file to a virus scanning website.

But yeah, I don't have to tell you all of this.

And yeah, since you guys are so smart you've already realized that it's not a virus.

So, now that you realize this, what do you think of the FREAKING SPREADSHEET?

Since you know how to code in VB, can you tell me how to return the currency symbol string? I can only retrieve numbers with that code. If I try to retrieve strings, I get an error.

I'm no programmer, so yeah.

this is a virus do not download it

you need to parse the json then you can pull whatever you want out of it like a dictionary object.

this is the json converter I use:
github.com/VBA-tools/VBA-JSON

then you can pull whatever you want out of it, ex:

Dim req As New WinHttpRequest
Dim resp As String
Dim g1 As Dictionary, g2 As Dictionary, g3 As Dictionary
Dim btc As String, eth As String, ethbtc As String

Call req.Open("GET", "api.gemini.com/v1/pubticker/btcusd")
Call req.send
resp = req.responseText
Set req = Nothing
btc = resp

...etc....

ActiveWorkbook.Worksheets("GEMINI").Range("B2") = btc
Set g1 = JsonConverter.ParseJson(btc)
ActiveWorkbook.Worksheets("GEMINI").Range("B3") = g1("result")
ActiveWorkbook.Worksheets("GEMINI").Range("B4") = g1("last")
ActiveWorkbook.Worksheets("GEMINI").Range("B5") = g1("bid")
ActiveWorkbook.Worksheets("GEMINI").Range("B6") = g1("ask")
ActiveWorkbook.Worksheets("GEMINI").Range("B7") = g1("volume")("BTC")
ActiveWorkbook.Worksheets("GEMINI").Range("B8") = g1("volume")("USD")
ActiveWorkbook.Worksheets("GEMINI").Range("B9") = g1("volume")("timestamp")

...

here's the coinmarketcap api results

Thanks for the help. I'll check this out right now.

Geez, I was hoping to keep it simple like my code already was, lol.

parsing the json is simpler in a way. once you get your skill level up.

some json doesn't like to be parsed because the way the delimiters are returned (the square [ and { curly bracket placement), but you can fix them fairly easily. this is what I do for what coin market cap returns:

Public Function BTCticker() As String
Dim req As New WinHttpRequest
Dim resp As String

Call req.Open("GET", "api.coinmarketcap.com/v1/ticker/bitcoin/")
Call req.send

resp = req.responseText

Set req = Nothing
BTCticker = Mid(resp, 2, Len(resp) - 2)

Exit Function
End Function

Yeah, I imagine it could. Maybe only have to make a few HTTP requests instead of a bunch like my code? It's getting a bit beyond me though. I'll look more into it later. Appreciate the info, man.

Also, still looking for suggestions for the actual spreadsheet from anyone. Anything you like or don't like?