<jupyter_start><jupyter_text>Dynamic Array UDFs  Why use them? `xlwings` has some great features for working with arrays where you may not know the size in advance. This is very useful in cases where you want to take the number of time periods in a model as an input. It could also be useful in a lot of other cases where you don't know the size of the data. E.g. you are calculating average portfolio returns, but you want the workbook to automatically pick it up when you add a new asset.  What are they exactly?  Array functions Array functions are actually nothing new. Excel has had support for array functions for a long time. An example of a built-in excel array function is `=MMULT`. You can pass it two cell ranges, and have those cell ranges multiplied together as matrices. The defining characteristic of an array function is that it returns multiple cells in its output.  Array UDFs This is just a UDF that returns multiple values. You call it in one cell, and the output will be in not only that cell, but other cells as well. To get a UDF to return multiple values, add `@xw.ret(expand='table')` above the function.   Dynamic UDFs These are UDFs that take advantage of an `xlwings` feature to resize the input. If you put `@xw.arg('my_arg', expand='table')` above your function, and have a parameter `my_arg` in your function, then you can select a single cell in Excel, and it will resize the input down and to the right until it hits blank cells. So this is a very easy way to reference a single cell, but get the entire table, regardless of the size of that table.   Dynamic Array UDFs These are dynamic UDFS that also return multiple values. So these will have `@xw.arg` and `@xw.ret` both with `expand`.  An Example Using Dynamic Array UDFs Let's take calculating a portfolio return as an example. Work off of the `portfolio_ret` example `xlwings` project. We want to be able to add a new asset to the `Data` sheet whenever we're adding another asset to the portfolio, but we don't want to have to update all our calculations to include it each time.  Average all the returns We can take advantage of the `DataFrame.mean` method to average all of the returns at once. Write a UDF as follows:<jupyter_code>@xw.func
@xw.arg('data', pd.DataFrame, expand='table')
@xw.ret(expand='table')
def average_all(data):
    return data.mean()<jupyter_output><empty_output><jupyter_text>Notice it has `@xw.func` which makes it a UDF. It also now has `@xw.arg('data', pd.DataFrame, expand='table')`. You can pass in `pd.DataFrame` as the second argument there to have the data come into the Python function as a `DataFrame`, which is very useful for our purpose here. You can also exclude that, instead have `@xw.arg('data', expand='table')`, and then it would come in as a list of lists. Notice also that we have added `@xw.ret(expand='table')`. This is because we are going to have multiple cells in the output, one for each average return, as well as one for each name of the asset. Call this function on a new sheet, referencing only the `Date` cell (top left cell) in the `Data` tab. You will see that it is able to calculate averages for the entire table of returns.  Other options with `pd.DataFrame`s You can also add `index=False` or `header=False` into either `@xw.arg` or `@xw.ret` when using them with `pd.DataFrame`. These will cause the index or column names, repsectively, to get cut off. If you have a `0, 1, 2...` coming back in your output that you don't want, pass `@xw.arg('data', pd.DataFrame, expand='table', index=False)`. If you wanted to remove the column names from the output, then `@xw.arg('data', pd.DataFrame, expand='table', header=False)` and you can also pass both: `@xw.arg('data', pd.DataFrame, expand='table', index=False, header=False)`  Get the Expected Return on the Portfolio Now we want to add some weights for how much the portfolio has in each asset. Then use the returns and weights to calculate an expected return on the portfoliolo. We want this function to work seamlessly as we add more assets. Add a weights column next to the output of the average returns. Add a weight for each asset, they should sum up to 100%. Now add the following UDF:<jupyter_code>@xw.func
@xw.arg('returns', expand='vertical')
@xw.arg('weights', expand='vertical')
def portfolio_return(returns, weights):
    er = 0
    for i, ret in enumerate(returns):
        er = er + ret * weights[i]
    return er<jupyter_output><empty_output><jupyter_text>The main logic here calculates the expected return on the portfolio. Call this function by referencing the first average return and the first weight. It will pick up all the returns and weights.  Other options with `expand` We explored `expand='table'` initially which will grab all cells in the table, going right and down from the referenced cell until it hits blank cells. We can also have this go only for a column or row. With `expand='vertical'`, it will grab all the values going down from the referenced cell until it hits a blank. It will not grab any cells to the right. With `expand='horiztonal'`, it will grab all the values going right from the referenced cell until it hits a blank. It will not grab any cells below the referenced cell. Here we used `expand='vertical'` because we only wanted one column of returns and one column of weights, separately, and they were next to each other in a table. If we had used `expand='table'` for the returns, it would also get the weights in that argument.  Add a New Asset Add another weight below the current weights, and adjust them so that they still sum to 1. To just see this work, you could copy the `Asset 1` column, place it to the right of the `Asset 2` column, and rename the header to `Asset 3`. Go back to the main sheet and recalculate the workbook by hitting `CTRL + ALT + F9`. You will see the new average return added, and the expected portfolio return will update for that asset as well.To see how we can generate the returns as well, follow the next section.  Generate Asset Returns To show an array UDF that doesn't utilize a `DataFrame`, we will generate the third asset's returns. First get this working in Python. Generate a single return from a normal distribution.<jupyter_code>import random

mean = 0.05
stdev = 0.1

random.normalvariate(mean, stdev)<jupyter_output><empty_output><jupyter_text>Now generate a list of returns from a normal distribution.<jupyter_code>num_rets = 5
all_rets = []
for i in range(num_rets):
    ret = random.normalvariate(mean, stdev)
    all_rets.append(ret)
all_rets<jupyter_output><empty_output><jupyter_text>Now wrap this all up into a function.<jupyter_code>def random_normal_returns(mean, stdev, num_rets):
    all_rets = []
    for i in range(num_rets):
        ret = random.normalvariate(mean, stdev)
        all_rets.append(ret)
    return all_rets

random_normal_returns(10, 20, 5)<jupyter_output><empty_output><jupyter_text>We have the function working in Python, now let's create the xlwings version.<jupyter_code>@xw.arg('num_rets', numbers=int)
@xw.ret(expand='table')
def random_normal_returns(mean, stdev, num_rets):
    all_rets = []
    for i in range(num_rets):
        ret = random.normalvariate(mean, stdev)
        all_rets.append(ret)
    return all_rets<jupyter_output><empty_output><jupyter_text>You will notice it is the same as the pure Python function, except we have added `@xw.arg('num_rets', numbers=int)` and `@xw.ret(expand='table')`.`@xw.ret(expand='table')` has been explained in the prior sections. We need this because we want to return multiple values, and have it come into multiple cells in Excel.`@xw.arg('num_rets', numbers=int)` is a little bit more tricky.  `numbers=int` I have been simplifying things when I say that Python has numbers as a type of data. It hasn't mattered thus far, but there are actually two (main) types of numbers in Python `int` for integers, e.g. 0, 1, 2... and `float` for numbers with decimals, e.g. 1.5, 2.0. We can see this by checking the type of a couple numbers.<jupyter_code>type(5)
type(5.0)
5 == 5.0<jupyter_output><empty_output><jupyter_text>We can see that even though it's the same number, and treated the same in a lot of contexts, `5` and `5.0` are actually different types, `int` and `float`, respectively.By default, when a number argument comes into our Python `xlwings` function from Excel, it will come in as a `float`. But we actually can't take a `range` of a `float` to do a loop, it has to be an `int`. You can see this here:<jupyter_code>for i in range(3):
    print('woo')
for i in range(3.0):
    print('woo')<jupyter_output><empty_output><jupyter_text>You can see we get `TypeError: 'float' object cannot be interpreted as an integer`, so the error is informing us that we have a `float` where we need an `int`. This is where `@xw.arg('my_arg', numbers=int)` comes into play. This will ensure that whatever number is coming into the Python code from Excel is converted into an `int`, so we can do loops over it. If you exclude it for an argument you want to use `range` on, or anything else that actually expects an `int`, then you will see the `TypeError: 'float' object cannot be interpreted as an integer` coming through in Excel.  Back to Generating Asset Returns Ok so let's go and try the `random_normal_returns` function from above. Add that function to your `portfolio_ret.py`. Make sure to add `import random` at the top as well. Then go and add the header "Asset 3" next to the asset returns and and run `=random_normal_returns(0.1, 0.2, 31)`. You should see the returns come horizontally.  Why is it Horizontal? Making it Vertical Before we had been returning `DataFrame`s from our functions. Those automatically are laid out into rows and columns. If we are just returning a list, xlwings must interpret this in some way to be able to create both rows and columns in the output. The way they have set this up is to use a list of lists. E.g. if you want to output to Excel 1, 2, 3 in one row, then 4, 5, 6 in the next row, then 7, 8, 9 in the third row to have a 3x3 table in the output, you would want to return this from your Python function:<jupyter_code>[
    [1, 2, 3]
]
[
    [1, 2, 3],
    [4, 5, 6],
    [7, 8, 9]
]<jupyter_output><empty_output><jupyter_text>If you merely return a single list, it is assumed that you want to output horizontally. So what do we do if we want a single column in the output rather than a single row? Well the output should look something like this:<jupyter_code>[
    [1],
    [2],
    [3]
]<jupyter_output><empty_output><jupyter_text>If we output in this format, then we will get the output vertically. So let's say we have an existing list, it's outputting horizontally, and we want to make it output vertically. What can we do? There is a simple list comprehension which accomplishes this for us.<jupyter_code>my_list = []
for a in [1, 2, 3]:
    my_list.append(a)
my_list
[ar for ar in my_orig_list]
my_orig_list = [1, 2, 3]
[[item] for item in my_orig_list]<jupyter_output><empty_output><jupyter_text>All we do is have a simple list comprehension where each item in the list comprehension is just wrapped in a list. Now we can add this to our existing function.<jupyter_code>def random_normal_returns(mean, stdev, num_rets):
    all_rets = []
    for i in range(num_rets):
        ret = random.normalvariate(mean, stdev)
        all_rets.append(ret)
    vertical_returns = [[ret] for ret in all_rets]
    return vertical_returns

random_normal_returns(10, 20, 5)<jupyter_output><empty_output><jupyter_text>We can see that the Python version of the function now outputs a list of lists, where each list is just containing a single item. This is the format we want to output vertically. Now let's add the decorators back, and bring this back into `portfolio_ret.py`.<jupyter_code>@xw.arg('num_rets', numbers=int)
@xw.ret(expand='table')
def random_normal_returns(mean, stdev, num_rets):
    all_rets = []
    for i in range(num_rets):
        ret = random.normalvariate(mean, stdev)
        all_rets.append(ret)
    vertical_returns = [[ret] for ret in all_rets]
    return vertical_returns<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0765369105423422, 'fraction_numerical': 0.007077606781334869, 'mean_word_length': 4.4395702775290955, 'pattern_counts': {'":': 0, '<': 53, '<?xml version=': 0, '>': 53, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '390132', 'license': 'MIT', 'n_tokens_mistral': 3554, 'n_tokens_neox': 3430, 'n_words': 1822, 'path': 'docsrc/source/_static/Extra Examples/xlwings/Dynamic UDFs/Dynamic Array UDFs.ipynb', 'repo_name': 'whoopnip/fin-model-course', 'size': '12151'}