Serving the Quantitative Finance Community

  • 1
  • 5
  • 6
  • 7
  • 8
  • 9
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

February 10th, 2014, 3:42 pm

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!
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

February 10th, 2014, 3:44 pm

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 tags on February 9th, 2014, 11:00 pm, edited 1 time in total.
 
User avatar
bearish
Posts: 5186
Joined: February 3rd, 2011, 2:19 pm

Excel tricks

February 11th, 2014, 2:41 am

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.
 
User avatar
MHill
Posts: 21
Joined: February 26th, 2010, 11:32 pm

Excel tricks

February 11th, 2014, 10:47 am

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.
 
User avatar
bearish
Posts: 5186
Joined: February 3rd, 2011, 2:19 pm

Excel tricks

February 11th, 2014, 11:07 pm

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).
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Re: Excel tricks

February 9th, 2021, 8:13 pm

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.  
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Re: Excel tricks

February 11th, 2021, 5:15 pm

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à!
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Re: Excel tricks

June 7th, 2021, 3:38 pm

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.
  
 
User avatar
Alan
Posts: 2958
Joined: December 19th, 2001, 4:01 am
Location: California
Contact:

Re: Excel tricks

June 7th, 2021, 3:57 pm

Rarely use Excel, but maybe this?
 
User avatar
bearish
Posts: 5186
Joined: February 3rd, 2011, 2:19 pm

Re: Excel tricks

June 7th, 2021, 7:30 pm

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.
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Re: Excel tricks

June 7th, 2021, 8:34 pm

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
 
 
User avatar
Cuchulainn
Posts: 20253
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Re: Excel tricks

June 8th, 2021, 9:06 am

    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.

    https://openpyxl.readthedocs.io/en/stable/
    from openpyxl import Workbook
    from openpyxl import load_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")
    wb = load_workbook("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")
    
    
     
    User avatar
    tags
    Posts: 3162
    Joined: February 21st, 2010, 12:58 pm

    Re: Excel tricks

    June 8th, 2021, 11:35 am

    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!
     
    User avatar
    Cuchulainn
    Posts: 20253
    Joined: July 16th, 2004, 7:38 am
    Location: 20, 000

    Re: Excel tricks

    June 8th, 2021, 12:18 pm

    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?
     
    User avatar
    bearish
    Posts: 5186
    Joined: February 3rd, 2011, 2:19 pm

    Re: Excel tricks

    June 8th, 2021, 4:08 pm

    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.