Excel function created using PyXLL throws #num error

Question

I am using Python with Pyxll to create a function in Excel which should return a timeline chart. Function takes two parameters -"names" and "dates"

when I tried to use this function in excel by selecting the range in "names" and "dates" columns, it throws "#num error. But I don't see any error on debugging the Python code. What could be the issue?

Here is my code:

 import matplotlib.pyplot as plt
 import numpy as np
 import matplotlib.dates as mdates
 from datetime import datetime

 from pyxll import xl_func

 @xl_func("str[] names, str[] dates")

def TimeLine_Plot(names, dates): 

  names = np.array(names)
  names = names.reshape(names.size)
  dates = np.array(dates)
  dates = dates.reshape(dates.size)

  dates = [datetime.strptime(ii, "%Y-%m-%dT%H:%M:%SZ") for ii in dates] 
  levels = np.array([-5, 5, -3, 3, -1, 1])
  fig, ax = plt.subplots(figsize=(8, 5))

  # Create the base line
  start = min(dates)
  stop = max(dates)
  ax.plot((start, stop), (0, 0), 'k', alpha=.5)

  for ii, (iname, idate) in enumerate(zip(names, dates)):
     level = levels[ii % 6]
     vert = 'top' if level < 0 else 'bottom'

     ax.scatter(idate, 0, s=100, facecolor='w', edgecolor='k', zorder=9999)
     # Plot a line up to the text
     ax.plot((idate, idate), (0, level), c='r', alpha=.7)
     # Give the text a faint background and align it properly
     ax.text(idate, level, iname,
         horizontalalignment='right', verticalalignment=vert, fontsize=14,
         backgroundcolor=(1., 1., 1., .3))

  ax.set(title="Matplotlib release dates")
  # Set the xticks formatting
  # format xaxis with 3 month intervals
  ax.get_xaxis().set_major_locator(mdates.MonthLocator(interval=3))
  ax.get_xaxis().set_major_formatter(mdates.DateFormatter("%b %Y"))
  fig.autofmt_xdate()

  # Remove components for a cleaner look
  plt.setp((ax.get_yticklabels() + ax.get_yticklines() +
       list(ax.spines.values())), visible=False)
  plt.show()

 return "Done!"

I think you forgot to formulate a problem and ask a question here. — ImportanceOfBeingErnest, Jan 03 '19 at 19:36
the issue basically is that, when I am using the function it returns #num error. instead it should return a timeline chart. — user3200076, Jan 03 '19 at 21:21
A python error is at least 5 lines long, mostly rather 20 or so. — ImportanceOfBeingErnest, Jan 03 '19 at 21:48
when I am running/debugging the code in canopy, don't see any error. when I am checking the function in excel, nothing happens it returns #num error. that's why I am confused where from the error is coming. — user3200076, Jan 03 '19 at 23:40
So the error is in excel?! That wasn't even clear from the question. I would recommend to [edit] it, to (a) give a clear problem description, (b) provide a runnable code, (c) show how you use it in excel. — ImportanceOfBeingErnest, Jan 03 '19 at 23:47
I have reframed the question, hope its clear. Also, I manage to solve this issue. Basically code was correct. The problem was in excel. The "dates" column was string but it should be in %Y-%m-%dT%H:%M:%SZ format. thanks for your time. — user3200076, Jan 04 '19 at 01:02
If you have solved this, would you add a self-answer below, in an answer box? Thanks! — halfer, Jan 05 '19 at 21:02

score 0 · Answer 1 · answered Jan 06 '19 at 17:26

Here is my answer. Remember to use date format in excel cell = %Y-%m-%dT%H:%M:%SZ

import matplotlib.pyplot as plt
import numpy as np
import matplotlib.dates as mdates
from datetime import datetime 
from pyxll import xl_func

@xl_func("str[] names, str[] dates") 
def TimeLine_Plot(names, dates):

     names = np.array(names)
     names = names.reshape(names.size)
     dates = np.array(dates)
     dates = dates.reshape(dates.size)

     dates = [datetime.strptime(ii, "%Y-%m-%dT%H:%M:%SZ") for ii in dates] 
     levels = np.array([-5, 5, -3, 3, -1, 1])
     fig, ax = plt.subplots(figsize=(8, 5))

     # Create the base line
     start = min(dates)
     stop = max(dates)
     ax.plot((start, stop), (0, 0), 'k', alpha=.5)

     for ii, (iname, idate) in enumerate(zip(names, dates)):
         level = levels[ii % 6]
         vert = 'top' if level < 0 else 'bottom'

         ax.scatter(idate, 0, s=100, facecolor='w', edgecolor='k', zorder=9999)
         # Plot a line up to the text
         ax.plot((idate, idate), (0, level), c='r', alpha=.7)
         # Give the text a faint background and align it properly
         ax.text(idate, level, iname,
         horizontalalignment='right', verticalalignment=vert, fontsize=14,
         backgroundcolor=(1., 1., 1., .3))

    ax.set(title="Matplotlib release dates")
    # Set the xticks formatting
    # format xaxis with 3 month intervals
    ax.get_xaxis().set_major_locator(mdates.MonthLocator(interval=3))
    ax.get_xaxis().set_major_formatter(mdates.DateFormatter("%b %Y"))
    fig.autofmt_xdate()

    # Remove components for a cleaner look
    plt.setp((ax.get_yticklabels() + ax.get_yticklines() +
          list(ax.spines.values())), visible=False)
    plt.show()

    return "Done!"

score 0 · Answer 2 · answered Jun 10 '19 at 13:01

0

To pass in dates, rather than the "str" type use the "date" type, eg:

@xl_func("str[] names, date[] dates")    
def TimeLine_Plot(names, dates):
    ....

See https://www.pyxll.com/docs/userguide/udfs.html#argument-types-and-return-types for details.

answered Jun 10 '19 at 13:01

Tony Roberts

387
1
5

Excel function created using PyXLL throws #num error

2 Answers2