OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Using an Excel Array Function from Python

  • Thread starter Thread starter therealchriswoodward
  • Start date Start date
T

therealchriswoodward

Guest
What I am attempting to do is use Python to open up a new Excel workbook, write some array functions in the first row and save the file so that when I open the workbook, my data is there. I'm using =STOCKHISTORY() to be specific.

I stripped the code down a lot to make it easy to read and understand what I am doing. I'm going to use the index of the list to get the data for several stocks. Here's my simplified code:

Code:
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

stocks = ["AAPL"]

ws['A1'] = f"=STOCKHISTORY(\"{stocks[0]}\",\"1/1/20\",\"1/1/24\",2,1,1)"

wb.save("C:/Users/there/Desktop/TEST.xlsx")

The problem is that because this is an array function, Excel adds the @ operator and what you see in Excel is:

=@STOCKHISTORY("AAPL","1/1/20","1/1/24",2,1,1)

This will only show "Close" in the cell. It doesn't fill the column with any data at all.

I have found that =SUM(2,2) will work just fine, so it is just array functions.

***Just an added note: My goal is to fill the list stocks[] up with ticker symbols and use {stocks[0]} to fill each column with the stocks just by changing the index. I wanted to get one to work first.
<p>What I am attempting to do is use Python to open up a new Excel workbook, write some array functions in the first row and save the file so that when I open the workbook, my data is there. I'm using =STOCKHISTORY() to be specific.</p>
<p>I stripped the code down a lot to make it easy to read and understand what I am doing. I'm going to use the index of the list to get the data for several stocks. Here's my simplified code:</p>
<pre><code>from openpyxl import Workbook

wb = Workbook()
ws = wb.active

stocks = ["AAPL"]

ws['A1'] = f"=STOCKHISTORY(\"{stocks[0]}\",\"1/1/20\",\"1/1/24\",2,1,1)"

wb.save("C:/Users/there/Desktop/TEST.xlsx")
</code></pre>
<p>The problem is that because this is an array function, Excel adds the @ operator and what you see in Excel is:</p>
<blockquote>
<p>=@STOCKHISTORY("AAPL","1/1/20","1/1/24",2,1,1)</p>
</blockquote>
<p>This will only show "Close" in the cell. It doesn't fill the column with any data at all.</p>
<p>I have found that <code>=SUM(2,2)</code> will work just fine, so it is just array functions.</p>
<p>***Just an added note: My goal is to fill the list stocks[] up with ticker symbols and use {stocks[0]} to fill each column with the stocks just by changing the index. I wanted to get one to work first.</p>
 

Latest posts

S
Replies
0
Views
1
Souvik Manna
S
S
Replies
0
Views
1
Shelling ford
S
Top