AltFolio FAQ for nonoobs

These are hints for powerusers, with experience in spreadsheets & coding.

For the general hints see /faq/ .

Really - if you are a beginner ... do NOT read this following page! It will only confuse you.
And you really do not need to know all this just to use AltFolio. Promised.

:-)


 

---------- Forwarded message ----------
Date: 24 February 2016 at 01:25

> so you dont need to build anything.
Very cool, you seem to be an able person. Happy to have another non-noob on board :-)

> CMC is 7h% or 24h%? (maybe not bug but just to be sure)
It is actually all available ones: 7days, 7hours, and 1hour.

check out the cells W5 - Y5
=askServer($W$3, join(",",A5:A9)&"/cmc/change7d","float")
=askServer($W$3, join(",",A5:A9)&"/cmc/change7h","float")
=askServer($W$3, join(",",A5:A9)&"/cmc/change1h","float")

> does cmc/marketcap exist in the server? 
yes, it is called "marketCap". Study cell V5, it looks similar to 
=askServer($U$3, join(",",A5:A9)&"/cmc/position","int")

-->  you could replace the /cmc/position with /cmc/marketCap (note the capital C)  similar to this:
=askServer($U$3, join(",",A5:A9)&"/cmc/marketCap","int")
or
=askServer($U$3, join(",",A5:A9)&"/cmc/marketCap","float")
and then it will show absolute numbers instead of the rank. Cool, isn't it?

> And my objective is to all the data in pie charts so I can see it easily. 
Go ahead.  And share you most beautiful results with me please. And write about it.  Make it known to others.  Thx.

> If you wish I can share it with you. 
Sure. Happy to see what you are doing with it

 

---------- Forwarded message ----------
Date: 24 February 2016 at 04:59

this is all the CMC data that you can query:

/cmc/position
/cmc/name
/cmc/marketCap
/cmc/price
/cmc/volume24
/cmc/change7d
/cmc/change7h
/cmc/change1h


> I'm getting confused. 
Patience :-)  you will solve it.

Importrange allows to pull data from another GoogleSheet-file (which can have different access rights).
Example:
=importrange("GoogleID-of-the-source-file","Portfolio!a4:ad11")
The you have to "allow access", and the data will flow ...

> I mean, I dont have to take the data from the server right?
You do.
>  It goes from the server, to your template and then to my database right? 
Yes, depends what you mean by template, but yes.

The flow is like this:
CMC/bittrex/poloniex/...  >--(API)-->  myDataserver 
then 
myDataserver  >--(serialkey)-->  yourAltFolioGoogleSheetCopy 
then 
yourAltFolioGoogleSheetCopy  >--(GoogleID)-->  your2ndGoogleSheetFile-with-importrange

(contact me, and I can share an example file with you)

If you really cannot manage, also no problem. Then give me access to your spreadsheet, so that I can have a look.

 

---------- Forwarded message ----------
Date: 24 February 2016 at 13:57

some background information.  Please consider getting whole columns, that saves time, and reduces the load on my dataserver.
And also I had made the experience that too numerous external calls can actually break the google tech, that is another reason to collect whole columns - like I do it in my GoogleSheet frontend.

i.e. instead of
   /DOGE/cmc/marketCap
   /DASH/cmc/marketCap
   /DOT/cmc/marketCap

call it as a column:

   /DOGE,DASH,DOT/cmc/marketCap

that is what in 
=askServer($W$3, join(",",A5:A9)&"/cmc/change7d","float")
the 
join(",",A5:A9)
is good for.

You see?

To better understand my  askServer  function, study the GoogleScript sourcecode, it is ~JavaScript:
   Menu ... Tools ... Script Editor ... altsheets-scripts.gs
It is not the tidiest code ever written, sorry - but it is simple enough, and partly commented well.

> Thanks for your help! 
> Really apreciate it!

Happy to help. It feels good that my work is now transformed into something new. Enjoy working on it! :-)

 

---------- Forwarded message ----------
Date: 24 February 2016 at 15:24

> [...] has invited you to comment on the following spreadsheet:
wow wow wow. I am deeply impressed!
First of all by the massive layout change. And it makes a lot of sense. And it looks great! Well done.
Secondly, how well my system is performing. You are asking my server to quench quite a bit of data. Wow, 25 lines. That's a lot. And - is it still working well? Really happy that I had done conceptional updates just recently,  that make the dataserver leaner and more stable.
Thirdly, all that other information. Cool, really.

My hints:
To move the "update" buttons (They are hiding the column heading titles): RightMouseClick on them once, then you can move them with LeftClickHold-and-move.

Important: What is your target audience? 
If only yourself, and you will not share this sheet - then no problem at all. 
But you put in so much work now, so I am wondering:  Is this spreadsheet only for yourself - or do you plan to share it with a larger group of people?
I am asking, because your personal AltFolio serial key is in it,  and visible to anyone who opening the sheet. There is no way of hiding it securely, as soon as you give access to a sheet file.
But in case the serial key gets stolen & abused, and I notice such behaviour on my dataserver, I would have to revoke your serial key - unnecessary hassle. Let us avoid that.

The way out is technical, and easy: IMPORTRANGE (see help page). So: In case you are planning to share that file with other people, please create an importrange solution, where you have 
(1) a PRIVATE sheet with the serial key, that is only visible to YOURSELF, which is updating from my data server
plus
(2) a PUBLIC sheet, which pulls the data via IMPORTRANGE, and which can be visible (READ-ONLY) to your friends/customers.
You see?

Next extensions
> but I'm finding problems to scale it.
There are many possible solutions for that, mentioned in this BCT post.

> Lack of colums available in the exchanges area (I can not put all of them)
What do you mean? The columns B-J ? You need more yellow columns there?

Exchange sums
> Not being able to calculate how much I've got in each exchange
You can. Look at the original MasterSheet, simply make a new copy. And then look into the cells B8:J8 - they do exactly that.

Column calls
> I'm getting the whole columns,
Very good. I had only mentioned it as a general hint: "columns faster than single calls"

/price
> the problem was that I set the coin names, then the markets and after that I have set the coins quantities.
Yes.  The price-from-exchanges function as columns ... actually has a slightly different syntax  than CMC - as you probably have noticed already, 
see cell O5 (in the original mastersheet)
=askServer($O$3,join(",",A5:A7)&"/"&join(",",N5:N7)&"/price","float")
where the inside
=join(",",A5:A7)&"/"&join(",",N5:N7)&"/price"
is evaluated to this syntax:
      BTC,DASH,DOGE/btc,poloniex,bittrex/price
that allows each coin price to be collected from a different exchange. Cool?

 

---------- Forwarded message ----------
Date: 20 January 2016 at 07:03

Thanks a lot. That is all really helpful.

>  I have to delete 06 field 
Hmmm ...  yes.  It works like this:
Field O5 is getting a whole COLUMN of data from the dataserver (that saves time), and populates itself AND all fields below. E.g.  O5 O6 O7 O8 O9 O10 O11 if you have 7 coins. That however breaks, if any of the fields below O5,  i.e. O6 O7 O8 O9 O10 O11 is containing any (manually typed) data. So yes, O6 must be empty. Only then the column can be filled by the formula in O5.

> I have to do this every time ...
That is strange. I am sure it was not like that in the past. [ ... coding ... ] It turned out it was a more general problem! I have now coded a fix for it. Very good. Thanks for pointing that out to me.

>  could you please add support for HitBTC exchange
I can. Not right now now, very busy these days. But soon!

Anything else, please tell me.

 

 

---------- Web pages----------

 

You can TRY OUT some of my products. Or if you just want to support me, please have a look at my GIVEBACK license.

 


http://altsheets.ddns.net/altfolio/faq/ - created on, and last change 24/Feb/2016