Serving the Quantitative Finance Community

• 1
• 5
• 6
• 7
• 8
• 9

tagoma
Posts: 18781
Joined: February 21st, 2010, 12:58 pm

### Excel tricks

QuoteOriginally posted by: edouardi have fornight data (2 data for each month) to put in a spreadsheet.are there fancy things Excel is able of when it comes to deal with dates such as "1H Jan-14"???OH!

tagoma
Posts: 18781
Joined: February 21st, 2010, 12:58 pm

### Excel tricks

Hi all.Is there a built in function or a simple trick to delete the content of all cells (say in a selection) that are NOT formulas?(something less ugly than a macro deleting the content of cells that do no contain the '=' character)
Last edited by tagoma on February 9th, 2014, 11:00 pm, edited 1 time in total.

bearish
Posts: 6631
Joined: February 3rd, 2011, 2:19 pm

### Excel tricks

The Excel VBA Range object has a property HasFormula, which is either true or false. By looping through each cell in your selection, you should be able to selectively delete the ones that come up false on this test.

MHill
Posts: 489
Joined: February 26th, 2010, 11:32 pm

### Excel tricks

QuoteOriginally posted by: edouardHi all.Is there a built in function or a simple trick to delete the content of all cells (say in a selection) that are NOT formulas?(something less ugly than a macro deleting the content of cells that do no contain the '=' character)Select your cellsCtrl + G (Go To command)Choose 'Special'Choose 'Formulas''OK'Delete Edit: Doh! re-read the question - choose 'Constants', not 'Formulas'
Last edited by MHill on February 10th, 2014, 11:00 pm, edited 1 time in total.

bearish
Posts: 6631
Joined: February 3rd, 2011, 2:19 pm

### Excel tricks

QuoteOriginally posted by: MHillQuoteOriginally posted by: edouardHi all.Is there a built in function or a simple trick to delete the content of all cells (say in a selection) that are NOT formulas?(something less ugly than a macro deleting the content of cells that do no contain the '=' character)Select your cellsCtrl + G (Go To command)Choose 'Special'Choose 'Formulas''OK'Delete Edit: Doh! re-read the question - choose 'Constants', not 'Formulas'Nice! It actually came in handy today in tracking down cells that suffered from conditional formatting (a potential source of many headaches).

tagoma
Posts: 18781
Joined: February 21st, 2010, 12:58 pm

### Re: Excel tricks

Ok. So I inherited of an old spreadsheet that was passed from hand to hand over time.
At opening this spreadsheet seems to try connecting to another spreadsheet as it shows "Linking: [filename.xls] ..." at the bottom of Excel. As you guessed, I have no clue what that other spreadsheet is.
I have found different "solutions" to remove this link (inspection of all types of objects in the spreadsheet, find/recplace, ...) on the internet but none has worked for me.
But maybe you guys have some day had the same issue and actually found a solution?
Many thanks.

tagoma
Posts: 18781
Joined: February 21st, 2010, 12:58 pm

### Re: Excel tricks

Ok. So I inherited of an old spreadsheet that was passed from hand to hand over time.
At opening this spreadsheet seems to try connecting to another spreadsheet as it shows "Linking: [filename.xls] ..." at the bottom of Excel. As you guessed, I have no clue what that other spreadsheet is.
I have found different "solutions" to remove this link (inspection of all types of objects in the spreadsheet, find/recplace, ...) on the internet but none has worked for me.
But maybe you guys have some day had the same issue and actually found a solution?
Many thanks.

Ok, so I finally found a solution. Actually, some Excel objects of type Name had their propery Visible set to False, hence this named Excel objects were not shown in the Name Manager. From there, loop over all Name objects and set them to visible. Voilà!

tagoma
Posts: 18781
Joined: February 21st, 2010, 12:58 pm

### Re: Excel tricks

What is the most straighforward way to run/launch a (python) script from within Excel?
Something around I would click on a Excel form control/activeX and it would run the script.
(the python code doesn't need to be embedded into the spreadsheet. both spreadsheet and python scripts are stored and will stay on the same server).
Your great suggestions very welcome! Thank you.

Alan
Posts: 10713
Joined: December 19th, 2001, 4:01 am
Location: California
Contact:

### Re: Excel tricks

Rarely use Excel, but maybe this?

bearish
Posts: 6631
Joined: February 3rd, 2011, 2:19 pm

### Re: Excel tricks

A lower tech solution would be to invoke it via a shell() command from a VBA sub. It’s an asynchronous call and you can’t directly communicate data from (or back to) the workbook, but it shouldn’t be too hard to work around that.

tagoma
Posts: 18781
Joined: February 21st, 2010, 12:58 pm

### Re: Excel tricks

thank you to both of you for suggestions. I do appreciate.
so I first went down the xlwings path. but setting the add-in, the Excel references, etc ... making sure all stars are well aligned is was bit annoying and didn't quite work. (don't get me wrong i like xlwings that I use intensively in other ways)
so I went down the shell way, based on this SO reply. i ended up with something like:

Function RunPython()
Dim obj As Object
Set obj = CreateObject("WScript.Shell")
RunPython = obj.Run("pythonw.exe C:/ ..../my_great_snippet.py", 0, True)
End Function

Cuchulainn
Posts: 64996
Joined: July 16th, 2004, 7:38 am
Location: Drosophila melanogaster
Contact:

### Re: Excel tricks

xlwings is kind of stone age. Clunky.

If your use cases are I/O in Excel (42!) then openpyxl might be an idea. The Excel model is a bit like VBA and C#.
Your specification doesn't say what Excel and Python are doing; are you using Excel as a database to be updated by Python code?
But maybe your use case is different.

from openpyxl import Workbook
import datetime

wb = Workbook()

ws = wb.active
ws1 = wb.create_sheet("Mysheet")
ws1 = wb.create_sheet("Tagoma")

wb.save("C:\ABC\XYZ\DD.xlsx")
ws1 = wb.create_sheet("Tagoma2")

ws1['A1'] = datetime.datetime(2021, 8, 29)
ws1.append([1, 2, 3,4])

print(wb.sheetnames)
wb.save("C:\ABC\XYZ\DD.xlsx")

from openpyxl import Workbook
wb = Workbook()

# grab the active worksheet
ws = wb.active

# Data can be assigned directly to cells
ws['A1'] = 42

# Rows can also be appended
ws.append([1, 2, 3,4])

# Python types will automatically be converted
import datetime
#ws['A2'] = datetime.datetime.now()

# Save the file
wb.save("C:\ABC\XYZ\sample.xlsx")


"Compatibility means deliberately repeating other people's mistakes."
David Wheeler

http://www.datasimfinancial.com
http://www.datasim.nl

tagoma
Posts: 18781
Joined: February 21st, 2010, 12:58 pm

### Re: Excel tricks

Hi Cuch. Thank you for suggestion.

Well my whole project is ill-designed due to many constraints from company's IT, users, management, etc....
I acquire data and perform a bunch of calculations on them with a python snippet, everything is then saved in a csv file. Another python snippet  then cherry picks data in that csv file and fills specific cells of specific sheets of the Excel workbook. There are other items in the spreadsheet  that I have to check manually. That is the spreadsheet has to be opened on my computer. Hence I thought maybe it would alleviate a bit main pain if I launch the python scripts from witihin the spreadsheet instead of searching the folder the are saved in  (I warned above this project is sick....)
The Shell solution (bearish) I have implemented does the job.

Can you please explain a bit why you think xlwings is clumsy? I mean maybe there are some use cases I shall aboid to use it.
Thank you!

Cuchulainn
Posts: 64996
Joined: July 16th, 2004, 7:38 am
Location: Drosophila melanogaster
Contact:

### Re: Excel tricks

I was confusing xlwings with XLw

https://xlw.github.io/

So, I take it back

Still, maybe a bit of redesign is an idea.........

many constraints from company's IT
non-sensical constraints?
"Compatibility means deliberately repeating other people's mistakes."
David Wheeler

http://www.datasimfinancial.com
http://www.datasim.nl

bearish
Posts: 6631
Joined: February 3rd, 2011, 2:19 pm

### Re: Excel tricks

I was confusing xlwings with XLw

https://xlw.github.io/

So, I take it back

Still, maybe a bit of redesign is an idea.........

many constraints from company's IT
non-sensical constraints?

As we discussed elsewhere, sometimes the ball of mud gets the job done.