I wish I made a spreadsheet but when I first started I never knew I would have so many keys so just made a Rich Text Document.....as least I do use colour coding and extra symbols as well as different sections to signify trading cards/value/owned/freebie/where I got it.
Comment has been collapsed.
I am also using excel. If you got the skills you can do everything you can imagine like conditional formatting of games you got several times, highlighting games you got as a gift or from a GA, ...
You also could import your own games from websites like steamcomp, steamdb, ..., use pivots, make charts where you left the most money on :) In VBA you can add a button that updates pivots and external data sources, ...
explanation:
columns: A = store site, B = bundle, C = date (highlighted orange = got it free, red = from steamgifts), D = game (blue when double), E = DRM, F = key, G = redeemed, H = order number, I/J = spent price in €/$, K/L = check if the game was from GA or free (column C is highlighted then), M = price according to raCHART (c/p from site)
So if I give away a key, I hightlight the game before in column F ("key") and write "SG and the GA-identifier) in the "redeemed" column. The conditional formatting will mark it ;)
other sliders: in ACHTUNG there are 2 pivots that check for games given away free (GA in column B and DRM steam) and if the store was steamgifts. So whenever I enter new games, they are highlighted once the pivots are updated so I can avoid giving away free games or regifting.
rest is wayne (tests) or for xbox and playstation :)
Problems you may be want to avoid:
Sometimes names are changing (esp. for GL games), so it could be clever to (also) use the gameID
Conditional formatting breaks when you add lines (e.g. with STRG + "+") with a different formatting and don't adopt to the previous line, use the brush that appears on the left
Comment has been collapsed.
could you upload a example file of this without the keys?
Comment has been collapsed.
Creating one is rather easy, should be possible for everyone. As your skills grow so does the spreadsheet - and vice versa. It would not be a good step to provide access to it ;) Better start and learn by doing, almost everyone needs Excel skills.
In case you got discrete questions you can ask ofc :)
Comment has been collapsed.
Well I am just interested in one thing. How did you do the column M for the price.
Is this a external Data source, cause you said its from rachart site?!?
Comment has been collapsed.
It is just copy paste from the columns of the ordered bundle's rachart.
E.g. here: here last column.
Personally I don't think that a script would be usefull as you needed the price when the bundle was out, not the current one. I am pretty sure there is some API to automate it but for me this is an overkill, especially as I - currently - do not use the price in any manner. I just save if for further use in the future.
Comment has been collapsed.
Ah thx. Well the rest is easy. I though you do collect the current prices dynamically.
Comment has been collapsed.
Update:
Meanwhile I do collect the current price. Just import your steamDB-profile as a new sheet (in my case "steam_DB_aut). "Data -> From Web" and select the complete site.
Afterwards add some columns (G to N) to export the contents of the strings:
Name Price / Hour euPrice Time Reviews
Half-Life 2 AppID: 220, Game Jul 99 9,99 € 1.3h 97%
To have the gamename use: =LEFT(B88,FIND(" AppID",B88,1)-1) asuming the data is in "B88"
German: =LINKS(B88;FINDEN(" AppID";B88;1)-1)
Same goes with the other datastrings:
AppID can be extracted with:
=IFERROR(MID(B88,FIND("AppID:",B88,1)+7,FIND(", Game",B88,1)-FIND("AppID:",B88,1)-7),"")
German: =WENNFEHLER(TEIL(B88;FINDEN("AppID:";B88;1)+7;FINDEN(", Game";B88;1)-FINDEN("AppID:";B88;1)-7);"")
As there are other types like DLC, Video, Application, Tools, Series do accordingly. They are written in that string, so you need to adopt your search string.
As I save the AppID in my main sheet:
=IFERROR(INDEX(steamDB_aut!A:I,MATCH(D3,steamDB_aut!G:G,0),MATCH("AppID",steamDB_aut!$A$8:$H$87,0)),"-")
German:
=WENNFEHLER(INDEX(steamDB_aut!A:I;VERGLEICH(D3;steamDB_aut!G:G;0);VERGLEICH("AppID";steamDB_aut!$A$87:$H$87;0));"-")
I can now ask for the corresponding price:
=IFERROR(INDEX(steamDB_aut!A:I,MATCH(D3,steamDB_aut!G:G,0),MATCH("euPrice",steamDB_aut!$A$88:$I$88,0)),"-")
IFERROR is used to avoid failed results like #NV or empty cells. The usage of index match match is a way to use dynamic VLOOKUP.
Each time you open the file, data is updated. You can trigger it using right click :)
Comment has been collapsed.
Thank you so much! Will have a look into it, when I got the time!
So far I extract the Appid with the official api link: https://api.steampowered.com/ISteamApps/GetAppList/v2/?key={YOUR_KEY}&format=xml
So I get all of them, even when I don't have it in my library. So thats cool for the key list. But I noticed that there is not everything listed. For example: 210943 should be Killing Floor - Community Weapon Pack 2, but there is no number available for it. So I wrote that down for myself.
I also tryed to get the current price from the api, but I failed on that part. So I will try your solution.
Thank you again! Auch für die deutsche Funktionen ;)
Comment has been collapsed.
I should have made some sort of list of my keys, every time I need one I have to start looking all over the place until I find it.
Comment has been collapsed.
txt file/notepad / I organised them though, by bundle site
Comment has been collapsed.
KeePass2, got my passwords there so why not my keys aswell
Comment has been collapsed.
At first I was using Notepad, but I switched to Excel a few months ago. Excel is so much better if you want to have your keys collection organized.
Comment has been collapsed.
until last month i used Google Docs with my revealed keys.
since this early month I use Google Sheets with only bundle gift links (eg: Indiegala)
it's easier for me to tracking which bundle i have bought or missed.
Comment has been collapsed.
MySQL database, with small website to view, add keys
Comment has been collapsed.
I think I'm like the only person who doesn't keep track. I just redeem stuff and give other stuff away. :)
Comment has been collapsed.
I write the keys on a paper then, just with a glance, I memorize it.
Right after I destroy the piece of paper and burn it. No proof will remain.
Then I start running. Bots are after me, they want my keys.
Finally I live by Jason Bourne's Steamgifts phylosophy of life.
Comment has been collapsed.
How I manage keys
First, I have in my email inbox, name tags as followed, to sort my emails :
- steam keys
- dispo (in english : available/ready), I directly tag any incoming emails with keys or links to bundle
- en cours (in english : pending / in process), I move a selection of previous emails I want to extract keys here
- used : I put here previous emails I processed.
- used perso : I used to store here emails with keys exclusively redeemed myself
Secondly, I maintain a spreadsheet on LibreOffice, with backup on cloud (google), to store keys I have extracted from emails.
- various sheets, mostly one per provider/reseller, plus various things like CV calculation, games that were free according to SG, etc...
- storing game name / keys / (eventually) steam link / if they can be gifted on SG / sources of key (online raffle/link to bundle / destination of key (if empty cell , -> key is not used, otherwise, main or alt account name i redeemed key on, or link of giveaway it was used in)
Now we need a tool to process massive giveaways from CVS-like files ^^
Comment has been collapsed.
GKeyBank:
- https://github.com/Alfred-Wallace/GKeyBank
- https://steamcommunity.com/groups/gkeybank
- you can simple create export-list and add it on your barter.vg "tradelist" profile.
- great program with few bugs (I reported them to author)
Comment has been collapsed.
Rofl. Thanks for links.
Managing keys can definitively be time consuming ^^
But I don't know if this tool can do things link filtering by bundles, adding additional columns like "is giftable on SG", etc, my spreadsheet already do all that natively...
I still have to test WEBSERVICE() function in my spreadsheet to get automatically some info :P ^^
I would definitively use that tool you mentioned if it could create giveaways for a given selection of keys.
Comment has been collapsed.
I don't know only one thing in github: where can I download the .exe file
Comment has been collapsed.
on steam group main page / dropbox.
I don't understand why creator uses dropbox concurrently to github, though...
Comment has been collapsed.
It's not my product. I reported to author ideas like that.
https://steamcommunity.com/groups/gkeybank/discussions/0/343786745996555465/
= You can add your own.
Comment has been collapsed.
I think these are awesome ideas. I think I will use this method to store keys ^^
Comment has been collapsed.
Thanks for your suggestion, I'm starting using this today !
Could you plz explain me how do you add a gift link (for example humble bundle gift) to a certain game ?
Comment has been collapsed.
Do you use normal notepad, pastebin or other methods?
I need a solution because I have lots of keys and I don't like put everything in the same notepad
Comment has been collapsed.