Print Page - GLBSE CSV -> profit & loss calculator?

Title: GLBSE CSV -> profit & loss calculator?
Post by: jackmaninov on May 19, 2012, 01:24:20 AM

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...

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: Sukrim on May 19, 2012, 08:08:50 AM

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.

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: Sukrim on May 19, 2012, 11:26:08 AM

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).

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: jackmaninov on May 19, 2012, 04:57:44 PM

Quote from: Sukrim on May 19, 2012, 08:08:50 AM

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?

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: Sukrim on May 20, 2012, 09:11:04 AM

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.

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: Sukrim on May 20, 2012, 12:28:54 PM

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

Thanks by the way!

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: Mushoz on May 20, 2012, 01:06:13 PM

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?

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: thirdlight on May 20, 2012, 02:06:59 PM

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

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: teek on May 20, 2012, 02:46:12 PM

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

:(

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

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: sunnankar on May 20, 2012, 02:58:58 PM

Quote from: znort987 on May 19, 2012, 09:55:42 AM

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

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: sunnankar on May 20, 2012, 03:00:08 PM

Quote from: Mushoz on May 20, 2012, 01:06:13 PM

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?

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: jackmaninov on May 20, 2012, 06:01:51 PM

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 :(

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: Sukrim on May 20, 2012, 06:25:30 PM

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

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: sunnankar on May 20, 2012, 11:54:05 PM

Quote from: znort987 on May 20, 2012, 03:14:57 PM

??? ... 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.

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: farfiman on June 06, 2012, 06:14:28 PM

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

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: vokain on July 03, 2012, 03:51:42 PM

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!

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: vokain on July 03, 2012, 08:46:45 PM

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

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: streblo on August 04, 2012, 08:06:06 PM

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? =/

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: labestiol on August 04, 2012, 09:20:23 PM

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.

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: EskimoBob on August 05, 2012, 11:28:48 AM

"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])

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: lethe on August 06, 2012, 11:01:55 AM

For me the API calls to GLBSE always timeout or 503, aborting execution.

The author has updated the fetchTicker macro (with a cached api fetch) but the update would need to be put into P&L calculator too.

link to git: https://github.com/znort987/glbse-googledoc-macro

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: Nefario on August 06, 2012, 12:42:11 PM

There is a limit on 10 requests to the API every minute.

This stops the server melting.

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: sebnow on August 06, 2012, 01:12:21 PM

Quote from: Nefario on August 06, 2012, 12:42:11 PM

There is a limit on 10 requests to the API every minute.

This stops the server melting.

Is this a global limit or per-IP? I'm having the same issues but I've restricted to less than 10 requests every minute. I understand GLBSE is getting pounded, but there's no point having an API if no one can really use it.

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: EskimoBob on August 06, 2012, 01:17:08 PM

Quote from: Nefario on August 06, 2012, 12:42:11 PM

There is a limit on 10 requests to the API every minute.

This stops the server melting.

At the moment, probably every API request hammers your db. Change it from "pull" to "push" and you can serve a huge pile of requests with no problems. Upgrade the API so it's not responding to every request with db query but answers with a pre-generated "statick page" - page that changes only when trade happens.
(4)

Title: Re: GLBSE CSV -> profit & loss calculator?
Post by: Nefario on August 06, 2012, 05:35:07 PM

Quote from: znort987 on August 06, 2012, 03:27:16 PM

Quote from: Nefario on August 06, 2012, 12:42:11 PM

There is a limit on 10 requests to the API every minute.

This stops the server melting.

That's because each API calls hits the DB.

If the caching was done at GLBSE instead
of client side, that would lighten the load
quite a bit.

Some parts of some API calls are cached, others not. Currently working on both graphs and API for some major improvements.

Bitcoin Forum

Economy => Securities => Topic started by: jackmaninov on May 19, 2012, 01:24:20 AM