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?

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.

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