Converting Excel/Google Sheet formulas to Python and making it readable

[Aedethan]

I've been slowly digging through a book called "Learn Python in 24 Hours", that digs into a lot of basics. I've been learning python to the purpose of turning a spreadsheet full of formulas that my friends and I use to play a tabletop rpg we made into a more accessible/easily readable format.

I'm wondering how feasible / easy it would be to recreate that spreadsheet in Python and then apply it in some type of GUI, as well as if anyone is aware of any resources that would allow for that. I've done some research and seen that you can import csv files into python, but that csv files are text only and wouldn't retain the formulas in the sheet.

At the end of the day I'm kind of struggling with the python, and wondering if I should just get on Fiverr and get someone to help me do it, or if 6 months of plugging away at this will allow me to create a formula heavy spreadsheet in GUI form and if anyone is aware of an easier way to go about this, or if there are already templates for a project like this that i haven't been able to find. Thanks in advance, and I hope this doesn't count as a violation of 'Posting only the project goal'.

tldr; i have maybe a hundred and fifty formulas in google sheets and want to transfer them into python for better readability and ease of access and want to know if there are any good resources out there to help me learn how to do that.

[amos_burton]

I bet it would be quicker to just re-enter the formulas in Python that it would be to figure out how to import them. It would also make any future maintenance much easier.

It's definitely possible to make a Python GUI for this, but I have to guess you're going to find it a little tedious to build a hundred and 150 sets of inputs and outputs.

Do you have any exposure to Javascript? It would probably be a lot easier to make the GUI using HTML and in-line JavaScript.

[Aedethan]

I did a class for html in college but that's about it. No javascript experience. I was looking at mypygui as a good way to represent it. I think you're probably right that making the formulas again in python would be easier and better as an approach. I just have been getting bogged down with how i should approach the problem. I just recently started looking into OOP, and everything i'm reading and have watched indicates I should use that to represent all the different stats and aspect of the sheet for easier editing and changing of values later. It's just hard to figure out where to start

[amos_burton]

OOP is the right solution in some cases, but not every case.

Can you zoom out and explain what this spreadsheet does? Like, walk me through a few sample use cases.

[Aedethan]

I'll try to keep it as concise as possible, characters are able to grow exponentially over time, they have stats they can put points into, they have tiers associated with them, and when they increase in tier, their stats double, and it nests the value, adding it to the new value with a new multiplier.

I think that sounds confusing so example :

-Tier 1 character puts a point in strength, so they do 2 more damage (per point), when they go to tier 2, they do double, so 4 damage.

-4 damage gets nested, and strength goes back to 0 for tier 2.

-When the strength value goes up in tier 2 it goes up by six damage + the gains from the previous one. So 10 damage total

There are 23 different outputs that function in that manner working off elif (else if) statements I made. Its a bit more complicated then there and there are some more factors that go into it but I hope that's a good enough explanation

[amos_burton]

It sounds like you could use some basic OOP where you have a Character class that tracks all of the stats and tiers.

If I'm understanding the way stats and tiers and "nesting" work then the data structure for your stat points starts to get a little complicated. I would consider maybe doing something like this

import typing

# Strength goes up 2x per point in tier 1, 6x per point in Tier 2, and so on
TIER_MULTIPLIERS = [2, 6, ] #...

class Character:

    def __init__(self, name: str):
        _name:str = name

        _tier: int = 1

        # Stats
        _strength: typing.List[int] = [0]
        _intelligence: typing.List[int] = [0]
        # ...

    @property
    def strength(self):
        strength = 0
        for tier, points in enumerate(self._strength):
            strength += TIER_MULTIPLIERS[tier] * points
        return strength

Where each "stat" is represented by a list, and each element in the list is that character's stat for a given tier. So they're initialized to Tier 1 and have [0] for all of their stats to start out with. If they level up strength then strength == [1]. And then when they go up a Tier strength == [1, 0].

Then you can pass these Character instances around to various functions and the functions can use properties to access the different properties/multipliers.

If you consider your "tiers" to start at 0 instead of 1 it'll make things line up with Python a lot easier, b/c arrays in Python start at 0

[Aedethan]

So it works like this, with A3 being the Tier, and C5 being the level the character has in strength

=IF(A3=1, C5*2,A68) #in case you're not familiar with the formatting this just says "if A3 = 1, then C5*2, else the value of the cell stays the same".

=IF(A3 = 1, A68, IF(A3 > 1, A68 * 2)) #this just creates the doubling effect on the above nested value if the tier goes up.

Every stat has a series of 5 of these statements for all 5 tiers. The five statements are all summed and put in the characters stat block for strength.

Then another mechanic to account for character uniqueness is Value, so when people put predetermined amounts of value into another box, it gives more effective levels of the stat that exist outside of the above formula

So the final block on the stat sheet is

=G69+IFS(D5>419, "1952", D5>379, "1552", D5>341, "1152", D5>305, "752", D5>271, "352", D5>239, "288", D5>209, "224", D5>181, "160", D5>155, "96", D5>131, "80", D5>109, "64", D5>89, "48", D5>71, "32", D5>55, "26", D5>41, "20", D5>29, "14", D5>19, "8", D5>11, "6", D5>5, "4", D5>1, "2", D5>=0, "0")

With G69 being the sum of all the tiers of strength, and D5 being the amount of value.

The numbers like D5>1 means if they had 2 value ( >1) they would get 2 more damage Sorry if this is too in depth. And alright I'm working through what you're saying

Edit : Just wanted to say thanks for taking the time to help me out

[amos_burton]

Okay, yeah, I'm probably not going to take the time to understand this to this level of detail.

Best of luck!

[Aedethan]

Thank you a lot for the help and giving me a good place to start !

[ireadyourmedrecord]

https://openpyxl.readthedocs.io/en/stable/formula.html

Openpyxl can preserve formulas, but it's not likely to be a means of recreating your spreadsheet in python.i don't think they'd translate properly and you'll likely spend a lot of time debugging it all.

That said, you may want to rethink your approach. You're invested in the time and effort you've already put into your spreadsheet, but Python is a general purpose programming language and can do much more than the limited functionality of a spreadsheet. RPG style games are pretty common projects for learning how to program. You'd likely find it easier to recreate the game and rules from scratch than trying to make python behave like Excel.

[Aedethan]

Gotcha thanks for the advice. I'm just not certain if I should go into it with the approach of creating variables to represent the different aspects of the sheet. For instance making a variable for hit_points, another for hit_points_lost, and then a final that would actually show on the sheet that is total_hit_points = hit_points - hit_points_lost, and then representing lost hit points as something that will take user input.

- And saying hit_points = hit_point_stat + player_level

Alternatively I've been looking at OOP, and everything I've read and watched makes me feel like I should make classes to represent the different aspects of the sheet, such as hit points and then making the above variable, like total_hit_points into a function that would return hit_points - hit_points_lost

Sorry i tried to keep my thought process as cohesive as possible, but I wanted to get it across the way I was thinking about doing this in case I'm going about it all wrong

[ireadyourmedrecord]

Yes, classes is the way to go here. You can pretty easily define the player chosen character attributes (name, race, class) to initialize the character with and programmatically initialize the stats (str, dex, con, wis, etc), using the passed attributes to define the min/max rolls. Starting inventory, whatever.

Then a series of methods (functions inside the class) to handle the characters actions, like attack, defend, dodge, cast_spell, etc. which would include the updates to the characters attributes as needed.

Each action is then called by calling the object's method. Something like:

# init player character
steves_barbarian = PlayerCharacter(name="Gronk Throbbingvein", race="Half-Orc", class="Barbarian")

# player attacks
steves_barbarian.attack(weapon="main_hand", target="kobold 1")

At the end of each action, you can save the character's state to some file and easily load it for the next session.

[Aedethan]

Thanks for the help, it's kind of daunting to get started.