GLBSE CSV -> profit & loss calculator?

[jackmaninov]

So I can download my GLBSE account history as a CSV file now. Who's going to be the first out the gate with a web app to track P&L?

Even cooler if we could get this through the GLBSE API...

[Sukrim]

Web app would be a bit weird (I personally won't upload any csv to a website with a list of addresses + trades...)

It would be cool to do a public spreadsheet template though for example.

[Sukrim]

Would be cool if we could access the CSV via the API (though that would mean we'd save our private API key on google servers for the requests...) - then it would be possible to directly import the CSV in google docs (there's a native function for that).

[jackmaninov]

Web app would be a bit weird (I personally won't upload any csv to a website with a list of addresses + trades...)

It would be cool to do a public spreadsheet template though for example.

I was thinking client-side javascript to interpret it, but an importer into google docs would work well too (especially if it could update through the API).

EDIT: Also, how can the calculator get current pricing if it isn't online?

[Sukrim]

You could parse the csv for any assets you still hold and then do requests to the public API for these to get current prices. Also you could ask for orderbooks instead to have a "if I sell ALL my assets right now down to 1 Satoshi, how much can the market eat up and what do I end up with?" number.

[Sukrim]

Could you put this on github? Might be easier to edit/improve there...

Thanks by the way!

[Mushoz]

Works great! Would be awesome if it would be possible to see how much the shares you are currently holding are worth at the 24h avg price.
Do you think that would be possible to implement?

[thirdlight]

Failed to cope with a "sellback" tx. Format is the same as a "sell", but type is "sellback".

[teek]

Couldn't locat column type in sheet 'Import' (line 19)




edit: was working with wrong spreadsheet, seems to be working fine now.

[sunnankar]

If anyone is interest, I'll try to convert this to a JavaScript
script for Google spreadsheets, shouldn't be too hard.

Could you whip up a ImportHTML function for the Final balance from Blockchain.info for Google Docs? Pretty please?

This gets it from Blockexplorer.com but the site seems to be unreliable at times and it requires two lookups.

Code:

C9=address

A1=ImportData("http://blockexplorer.com/q/getreceivedbyaddress/"&C9)

A2=ImportData("http://blockexplorer.com/q/getsentbyaddress/"&C9)

Current Balance equals = A1-A2

[sunnankar]

Works great! Would be awesome if it would be possible to see how much the shares you are currently holding are worth at the 24h avg price.
Do you think that would be possible to implement?

Should use both the 24h and 5 day average price. The 5 day average price is probably more representative of the actual price you could sell at due to the current amount of volume. But then why not have both?

[jackmaninov]

Wow, great work. I've left a 1BTC tip to encourage further development

I'm a bit disappointed with GLBSE not having any kind of legacy transaction in my history for my migrated GLBSEv1 account

[Sukrim]

Old data will come sooner or later too, at least we now have the current one!

[sunnankar]

... not entirely sure why that is needed in the context of the GLBSE importer ?

Also, why don't you fallback on blockchain.info when blockexplorer.com fails ?

Not really needed for the importer but would be a helpful formula to have and I have been fumbling around with blockchain.info but not been able to get it to work. One option:

Code:

C104 = Address

=ImportHtml("http://blockchain.info/address/"&C104, "table", 2)

Would be nice to have just the Final Balance as the output.

[farfiman]

I get this:

Oops
Unknown transaction type on row 42 ... aborting. (line 109)
OK

The type is "sellback"  which happens when for example  PPT.x does forced buyback

EDIT: for now I just change all 'sellback' to 'sell' and it works fine

[vokain]

i tried running it today (from the code on your first page post) and I get the same sellback error. I'm going to try running it after changing all the sellbacks to sells and let you know!

[vokain]

Unfortunately after a run through a ~1900 record CSV the script froze up and said maximum execution time reached or something along the lines

[streblo]

pnlCalc is barfing at the very end of the script. The headers of PNL, "asset shares etc..." become bold and then the spreadsheet says "Script pnlCalc experienced an error Dismiss".

Any ideas? =/

[labestiol]

Close to the same bug, but i do have a random number of lines of assets in the PNL sheet, then it fails

EDIT : Problem seems to be random bugs on the GLBSE API call.

[EskimoBob]

"Script pnlCalc experienced an error"

This what I see on the Execution transcript log:

Code:

SpreadsheetApp.getActiveSpreadsheet()
Spreadsheet.getSheetByName([Import])
SpreadsheetApp.getActiveSpreadsheet()
Spreadsheet.setActiveSheet([Sheet])
Sheet.getLastRow()
Sheet.getLastColumn()
Sheet.getRange([1, 1, 1, 8])
Range.getLastColumn()
Range.getCell([1, 1])
Range.getValue()
....

Range.getCell([1, 7])
Range.getValue()
Range.getCell([1, 8])
Range.getValue()
SpreadsheetApp.getActiveSpreadsheet()
Spreadsheet.getSheetByName([PNL])
SpreadsheetApp.getActiveSpreadsheet()
Spreadsheet.insertSheet([PNL])
Sheet.clear()
SpreadsheetApp.getActiveSpreadsheet()
Spreadsheet.setActiveSheet([Sheet])
Sheet.appendRow([[asset, shares, invested, dividend, 24hAvgPrice, 5dAvgPrice]])
Sheet.getRange([1:1])
Range.setFontWeight([bold])
Sheet.setFrozenRows([1])
UrlFetchApp.fetch([https://glbse.com/api/asset/GLBSE_CASH])
HTTPResponse.getContentText()
Sheet.appendRow([[GLBSE_CASH, 0.0, 15.618193739999999, -0.0, 0.0, 0.0]])
UrlFetchApp.fetch([https://glbse.com/api/asset/WALLT_CASH])