Message Boards Message Boards

Stock allocation spreadsheet using EmbedCode

Chad Knutson, Wolfram Research
Posted 10 years ago

Let's assume that we have a spreadsheet that has the various stocks and number of shares of each stock that we own. I want to make a pie chart that shows how my wealth is distributed amongst my stocks. To do this, I will make use of an embedded api functions that calls the WolframCloud to get current stock prices.

First, we'll deploy an APIFunction that returns the price of the stocks.

 co = CloudDeploy[APIFunction["comp" -> "Company", FinancialData[#comp] &], Permissions -> "Public"]

Let's make sure that this works in Mathematica using URLExecute with a selected company name:

In[23]:= URLExecute[co, {"comp" -> "aapl"}]

Out[23]= 116.64

That looks correct. Next, call EmbedCode with option "Googledocs" to get a function that can be called within our spreadsheet

EmbedCode[co, "GoogleDocs"]

Copy the returned code to the "script editor" of your google spreadsheet. The default name of the function is wolframCloudCall, which I don't change. Now, to get a result, I need to type =wolframCloudCall(-cell with company name-) into a cell, and the result will be returned to that cell.

enter image description here

I have created a Google Spreadsheet that has several stocks that I'm pretending to own. The spreadsheet with pie chart (created using columns A and B) now look like this, where column C contains the function calls to WolframCloud. enter image description here
POSTED BY: Chad Knutson
5 Replies
Sort By:

Hello Chad, nice example thanks, In the Mathematica help "GoogleDocs" is not mentioned yet. Only when I use $EmbedCodeEnvironments it is mentioned.

One of the things I am searching for is how make just one cloud function call, get a list of lists returned and then show every value in its own cell.

The questions is how to show a matrix type result in each in its own spreadsheet cell. This would speed up the results since the communication overhead per cloud call is quite high.

Do you know how to do this in a Google spreadsheet or in a XL spreadsheet.
POSTED BY: Pieter van Bijnen

Hi Pieter,

I'm sure this can be done, but right now I don't know how. I'll post a solution when I have it.

Returning values in across multiple cells can be done in a google spreadsheet using arrayformula (e.g., =arrayformula(a1:a2) will return the values of cells a1 and a2 in 2 different cells). Surely excel has something similar.

Now to tie this all together....
POSTED BY: Chad Knutson

This can be made to work with some specifications on our functions and a minor tweek to the results from EmbedCode

First, the WolframCloud APIFunction must be made to work with a list of companies rather than a single company. 

co = CloudDeploy[APIFunction["comp" -> DelimitedSequence["Company", ","], FinancialData /@ #comp &, "JSON"], Permissions -> "Public"]

Note that DelimitedSequence tells the function to expect multiple values. Also, the output format is now specified as JSON, which works nicely with google spreadsheet.

Now create the embedded code as before

EmbedCode[co, "GoogleDocs", ExternalFunctionName -> "WCFinance",  ExternalTypeSignature -> {{"String"}, "Array"}]

I have named the function WCFinance. The javascript types for the input and output for my api are now specified.

I assume that we'll want to call our function using reference cells (e.g., =WCFinance(A2:A8)). Since my APIFunction is expecting a string for input, I need to modify the embedded code to convert the cell inputs to a string. This trivial to do. Just call join() on the input variable "comp". The modified line of code look like this:

_payload["comp"] = comp.join();

Now I can call the function with multiple inputs, and the output is automatically written to the correct cells. enter image description here
POSTED BY: Chad Knutson

Chad,

Thanks again. Work perfect and much more reliable than the "single value per formula" version. If you drag the "single value function" to populate many cells at ones is seems to fire all those requests almost at the same time to the WolframCloud. This results in some requests are not handled/ served at all. So you second version solves speed and reliability.

I'am still struggeling with the Excel version:

EmbedCode[co, "VisualBasic"] produces somthing like this:

Imports System.Net
Imports System.IO
Imports System.Text

Public Class Wolfram_Cloud

    Public Function Wolfram_Cloud_Call(ByVal comp As String) As String

        Dim data As String = "comp=" + CStr(comp)
        Dim encoding As New UTF8Encoding
        Dim byteData As Byte() = encoding.GetBytes(data)

        Dim request As HttpWebRequest = DirectCast(WebRequest.Create("http://www.wolframcloud.com/objects/b7d80951-163d-4e03-a54e-0b7cd975055a"), HttpWebRequest)
        request.Method = "POST"
        request.ContentType = "application/x-www-form-urlencoded"
        request.Referer = "http://www.wolframcloud.com/objects/b7d80951-163d-4e03-a54e-0b7cd975055a"
        request.UserAgent = "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:26.0 Gecko/20100101 Firefox/26.0"
        request.KeepAlive = True
        request.ContentLength = byteData.Length

        Dim requestStream As Stream = request.GetRequestStream()
        requestStream.Write(byteData, 0, byteData.Length)
        requestStream.Close()

        Dim postResponse As HttpWebResponse
        postResponse = DirectCast(request.GetResponse(), HttpWebResponse)
        Dim requestReader As New StreamReader(postResponse.GetResponseStream())

        Dim result As String = requestReader.ReadToEnd       
        Return result

    End Function

End Class

I figured out it is a Class and therefore I need to copy it in Excel's "Class Modules" section. Next I think i need to fire the instance but I am out of my comfort zone now. Could you explain how to fire this from Excel?
POSTED BY: Pieter van Bijnen

I'm very glad that method for googledocs worked for you, Pieter.

As you have learned, the VisualBasic option is not directly useable by Excel. I think that an EmbedCode option for Excel is being planned, but I have no idea when it would be available.

Another user (Rodrigo Murta) created a basic function for Excel that might work for you. See his reply in this thread. I haven't tested it personally, so I don't know if modifications are needed for multiple inputs.
POSTED BY: Chad Knutson
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Tag limit exceeded
Note: Only the first five people you tag will receive an email notification; the other tagged names will appear as links to their profiles.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract