GLBSE CSV -> profit & loss calculator?

Nefario

hero member

Activity: 602

Merit: 513

GLBSE Support [email protected]

Quote from: ?? on ??

Quote from: Nefario on August 06, 2012, 07:42:11 AM

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.

EskimoBob

legendary

Activity: 910

Merit: 1000

Quality Printing Services by Federal Reserve Bank

Quote from: Nefario on August 06, 2012, 07:42:11 AM

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)

sebnow

newbie

Activity: 33

Merit: 0

Quote from: Nefario on August 06, 2012, 07:42:11 AM

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.

Nefario

hero member

Activity: 602

Merit: 513

GLBSE Support [email protected]

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

This stops the server melting.

lethe

newbie

Activity: 51

Merit: 0

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

EskimoBob

legendary

Activity: 910

Merit: 1000

Quality Printing Services by Federal Reserve Bank

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

labestiol

sr. member

Activity: 434

Merit: 251

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.

streblo

full member

Activity: 165

Merit: 100

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

vokain

legendary

Activity: 1834

Merit: 1019

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

vokain

legendary

Activity: 1834

Merit: 1019

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!

farfiman

legendary

Activity: 1449

Merit: 1001

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

sunnankar

legendary

Activity: 1031

Merit: 1000

Quote from: ?? on ??

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

Sukrim

legendary

Activity: 2618

Merit: 1007

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

jackmaninov

full member

Activity: 139

Merit: 100

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 Sad

sunnankar

legendary

Activity: 1031

Merit: 1000

Quote from: Mushoz on May 20, 2012, 08:06:13 AM

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?

sunnankar

legendary

Activity: 1031

Merit: 1000

Quote from: ?? on ??

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

teek

hero member

Activity: 667

Merit: 500

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

Sad

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

thirdlight

sr. member

Activity: 445

Merit: 250

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

Mushoz

hero member

Activity: 686

Merit: 500

Bitbuy

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?

Sukrim

legendary

Activity: 2618

Merit: 1007

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

Thanks by the way!

Sukrim

legendary

Activity: 2618

Merit: 1007

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.

jackmaninov

full member

Activity: 139

Merit: 100

Quote from: Sukrim on May 19, 2012, 03: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?

Sukrim

legendary

Activity: 2618

Merit: 1007

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

Sukrim

legendary

Activity: 2618

Merit: 1007

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.

jackmaninov

full member

Activity: 139

Merit: 100

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

Topic: GLBSE CSV -> profit & loss calculator? (Read 3442 times)